DECLARE
@sql VARCHAR(2048)
,@sort INT
DECLARE
tmp CURSOR FOR
/*********************************************/
/********* DB CONTEXT STATEMENT *********/
/*********************************************/
SELECT '--
[-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [--
SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT ''
AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB USER CREATION *********/
/*********************************************/
SELECT '--
[-- DB USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF NOT EXISTS (SELECT [name] FROM
sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ')
BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' +
QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' +
QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS
--],
4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE
[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '--
[-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
5 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 --],
6 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) IN (
--get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G =
Windows group
)
--ORDER
BY rm.role_principal_id ASC
UNION
SELECT ''
AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '--
[-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
8 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(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 --],
9 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 = @OldUser
--ORDER
BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT ''
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '--
[--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
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 --],
12 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id =
usr.principal_id
--WHERE
usr.name = @OldUser
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 4 -- 0 to 4
are system users/schemas
AND [usr].[type] IN ('G', 'S', 'U') -- S =
SQL user, U = Windows user, G = Windows group
UNION
SELECT ''
AS [-- SQL STATEMENTS --],
13 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '--
[--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
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 --],
15 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
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
