Encrypting connections to SQL Server – SSL

The post is to demonstrate how to encrypt connection to SQL Server using SSL. At the bottom of the page you can see the video which demonstrates how to create a certificate and configure SQL Server to use SSL. SSL : Secure Sockets Layer is the most commonly used for web based client or native […]

Script out Server – Database Level Permission for all users and from all databases

This is always an issue that all the permission are gone when ever databases are refreshed from a backup.The below script will script Server ,Database,Object and Database Role Level Permission for all Databases and all users. Create a  ServerList.txt Download the Script_Server_Database_Level_Permission.sql and Powershell Script and save it to local drive location. Replace the “Path” with correct […]

Enable / Disable Users In Database

I normally see this issue when connect permission is removed/revoked from the database USE DatabaseName GO GRANT CONNECT TO LoginName You can check which users in a database have the CONNECT permission by executing this T-SQL: USE DatabaseName GO SELECT name, hasdbaccess FROM sys.sysusers WHERE name = ‘LoginName’

Security Part : (Server Level Permission)

Get list of built-in securable classes SELECT * FROM fn_builtin_permissions(default) use master go select * from fn_my_permissions(‘test_replogin’,’USER’) go use master go select * from sys.server_principals where name =’test_replogin’ select * from sys.server_permissions where grantee_principal_id=270 go SELECT pr.principal_id, pr.name, pr.type_desc, pe.state_desc, pe.permission_name FROM sys.server_principals AS pr JOIN sys.server_permissions AS pe ON pe.grantee_principal_id = pr.principal_id where pr.name=’test_replogin’

Understanding Shared Folders and Windows Firewall

Understanding Shared Folders and the Windows Firewall http://technet.microsoft.com/en-us/library/cc731402.aspx   Sharing a folder or file creates a Windows Firewall exception for File and Printer Sharing. The exception opens the ports listed in the following table. Connection Ports TCP 139, 445 UDP 137, 138 The default scope is to allow access from any computer on the network, […]

Database user Permission and Server Role

The script pulls user database and server level permission Script DECLARE @DB_USers TABLE (DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime) INSERT @DB_USers EXEC sp_MSforeachdb ‘ use [?] SELECT ”?” AS DB_Name, case prin.name when ”dbo” then prin.name + ” (”+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =”?” and owner_sid!=”sa”) + ”)” else […]

Script to grant permission on database objects to user or role

Script :Grant Permisison on User Object to User (Read Only Permission) A Handy script to quickly script to grant permission on database objects to user or role go use databasename go –Select Permisison on User Table— DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT […]