Script Database Logins and Roles

The fastest way to rebuild or document the security setup of a database. The first query walks sys.database_principals and generates a CREATE USER statement for each one - handling SQL users, Windows users and Windows groups differently. The second query joins in sys.database_role_members and generates an sp_addrolemember call for every role membership, skipping fixed roles and internal principals.

--Logins.
SELECT
	CASE prin.type_desc
		WHEN 'SQL_USER' THEN 
			'CREATE USER [' + prin.name + '] FOR LOGIN [' + prin.name + ']'
		WHEN 'WINDOWS_GROUP' THEN 
			'CREATE USER [' + prin.name + ']'
		WHEN 'WINDOWS_USER' THEN 
			'CREATE USER [' + prin.name + '] FOR LOGIN [' + prin.name + ']'
	END as [Script],
	prin.name as [LoginName],
	prin.type_desc AS [LoginType]
FROM
	sys.database_principals prin
WHERE
	prin.type_desc <> 'DATABASE_ROLE'
	AND prin.name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')

--Database roles.
SELECT
	'EXEC sp_addrolemember N''' + USER_NAME(mem.role_principal_id) + ''', N''' + prin.name + '''' as [Script],
	prin.name as [LoginName],
	prin.type_desc AS [LoginType],
	USER_NAME(mem.role_principal_id) AS AssociatedRole
FROM
	sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem
	ON prin.principal_id=mem.member_principal_id
WHERE
	prin.type_desc <> 'DATABASE_ROLE'
	AND USER_NAME(mem.role_principal_id) IS NOT NULL
	AND prin.name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
	AND prin.sid IS NOT NULL
	and prin.sid NOT IN (0x00)
	and prin.is_fixed_role <> 1
	AND prin.name NOT LIKE '##%'