Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

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

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...