Added support for script generation of a given db principal name.
FYI regarding authentication_type
Keep an eye on here: https://msdn.microsoft.com/en-us/library/ms187328%28v=sql.90%29.aspx
It's only supported from 2012+ so it's not included in the following script.
DECLARE
@sql VARCHAR(2048)
,@sort INT
,@DB_USER VARCHAR(128)
SET @DB_USER = '' --specify database principal name or keep empty to script all users
DECLARE tmp CURSOR FOR
/*********************************************/
/********* SERVER INFO *********/
/*********************************************/
SELECT '-- [-- INFO HEADER --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
Select '-- Script erstellt für Datenbank ' + DB_NAME(DB_ID()) + ' am ' + CONVERT(nvarchar(16), GETDATE(), 121) + ' von ' + SYSTEM_USER + ' auf Server ' + @@servername As [-- SQL STATEMENTS --]
,1 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB CONTEXT STATEMENT *********/
/*********************************************/
SELECT '-- [DATENBANK] --' AS [-- SQL STATEMENTS --],
1.1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'USE ' + SPACE(1) + QUOTENAME(DB_NAME()) + ' 'AS [-- SQL STATEMENTS --],
1.2 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* MAP ORPHANED USERS *********/
/*********************************************/
SELECT '-- [-- ORPHANED USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'ALTER USER [' + rm.name + '] WITH LOGIN = [' + rm.name + ']',
4
FROMsys.database_principals AS rm
Inner JOIN sys.server_principals as sp
ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sid
WHERE rm.[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
AND rm.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')
AND (rm.name = @DB_USER OR @DB_USER = '')
UNION
/*********************************************/
/********* DB USER CREATION *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ''' + rm.[name] + '''' + ') BEGIN CREATE USER ' +
QUOTENAME(rm.[name]) + ' FOR LOGIN ' + QUOTENAME(sp.[name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(IsNull(rm.default_schema_name, 'dbo')) + ' END;',
6 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_principals AS rm
LEFT JOIN sys.server_principals as sp
ON rm.sid = sp.sid
WHERE rm.[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
AND rm.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')
AND (rm.name = @DB_USER OR @DB_USER = '')
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
8 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_role_members AS rm
WHEREUSER_NAME(rm.member_principal_id) IN (
--get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [name] NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')
AND [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
AND (name = @DB_USER OR @DB_USER = '')
)
--ORDER BY rm.role_principal_id ASC
UNION
/*********************************************/
/********* TYPE LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- TYPE LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
9.1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(tp.schema_id)) + '.' + QUOTENAME(tp.name) --select, execute, etc on specific objects
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
9.5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
INNER JOIN sys.types AS tp
ON perm.major_id = tp.user_type_id
INNER JOIN sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE (usr.name = @DB_USER OR @DB_USER = '')
UNION
SELECT '' AS [-- SQL STATEMENTS --],
9 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
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) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
11 AS [-- RESULT ORDER HOLDER --]
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 (usr.name = @DB_USER OR @DB_USER = '')
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
12 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
13 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
14 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
--WHEREusr.name = @OldUser
WHERE[perm].[major_id] = 0
AND usr.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')
AND [usr].[type] IN ('G', 'S', 'U', 'R') -- S = SQL user, U = Windows user, G = Windows group, R = Database role
AND (usr.name = @DB_USER OR @DB_USER = '')
UNION
SELECT '' AS [-- SQL STATEMENTS --],
15 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
16 AS [-- RESULT ORDER HOLDER --]
UNION
SELECTCASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
17 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.major_id = s.schema_id
inner join sys.database_principals dbprin
on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema
AND (dbprin.name = @DB_USER OR @DB_USER = '')
ORDER BY [-- RESULT ORDER HOLDER --]
OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
FETCH NEXT FROM tmp INTO @sql, @sort
END
CLOSE tmp
DEALLOCATE tmp