Blog Post

SQL security scripting - TSQL

,

SQL DBA often needs to script SQL Logins,Server roles,database users and Roles. Although its easy to script login,roles and users with SSMS, associating login to server Roles and database users to db roles is not straight forward using Script Wizard. We can easily overcome this using below TSQL scripts.

/*ASSIGN SERVER ROLES TO LOGIN*/

SELECT

'EXEC sp_addsrvrolemember '

+ SPACE(1) + QUOTENAME(rm.name, '''')+','

+ SPACE(1) + QUOTENAME(rm1.name, '''') AS '--Role Memberships'

from sys.server_role_membersa

INNER JOIN

sys.server_principals rm ON a.member_principal_id =rm.principal_id

INNER JOIN

sys.server_principals rm1 ON a.role_principal_id = rm1.principal_id

-----------------------------------------------------------
/*CREATE DATABASE USERS*/

SELECT 'CREATE USER [' + a.name + '] for login [' + b.name + ']'  from sys.database_principals a

INNER JOIN sys.server_principals b ON a.sid = b.sid

where a.name <> 'dbo'

-----------------------------------------------------------
/*CREATE DATABASE ROLES*/

SELECT 'CREATE ROLE [' + a.name + '] AUTHORIZATION [' + b.name + ']' from sys.database_principalsa

INNER JOIN sys.database_principals bON a.owning_principal_id = b.principal_id

where a.type = 'R' and a.is_fixed_role <> 1

GO

------------------------------------------------------------
/*ASSIGN ROLES TO USER*/

SELECT --rm.role_principal_id,

'EXEC sp_addrolemember @rolename ='

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')

+ ', @membername ='+ SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'

FROM sys.database_role_membersAS rm

ORDER BY rm.role_principal_id

GO

------------------------------------------------------------
/*SELECT OBJECT LEVEL PERMISSION*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissionsAS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id= usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

ORDER BY usr.name

------------------------------------------------------------
/*SELECT OBJECT LEVEL PERMISSION FOR A OBJECT*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissionsAS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id= usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

Where obj.name = 'ULTIMATES_CURRENT'

ORDER BY usr.name

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating