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_members a
	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_principals a
	INNER JOIN sys.database_principals b ON 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_members AS 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_permissions AS 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_permissions AS 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

view raw SQLTechnet - SQLSecurityScript.sql hosted with ❤ by GitHub

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.