October 2, 2015 at 6:27 am
change in DB USER CREATION:
old:
CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN ('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + ', SID = ' + CONVERT(varchar(1000), sid) + SPACE(1) + ' END; ')
new:
CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN ('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + ', SID = ' + CONVERT(varchar(1000), sid,1) + SPACE(1) + ' END; ')
October 29, 2015 at 10:07 am
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
January 8, 2016 at 12:51 pm
Hi,
I am trying to pull the Object level permissions for all the databases at one shot but I am unable to do.
Please help me
January 14, 2016 at 2:50 am
Can we use this script with 'sp_msforeachdb' ? so that we can get a output for all databases on instance level ?
February 26, 2016 at 12:00 pm
Hi Steve,
This is an excellen script. However, when I try to use this on SQL Server 2008 R2, It gives me an error:
"
Msg 207, Level 16, State 1, Line 63
Invalid column name 'authentication_type'.
"
The problem is that there is no column with that name in sys.database_principals in SQL 2008 R2. This column was added in later versions.
Gaganpreet S Lamba
February 26, 2016 at 2:30 pm
salil.dixit1984 (1/14/2016)
Can we use this script with 'sp_msforeachdb' ? so that we can get a output for all databases on instance level ?
You could give it a shot. I haven't coded it to work across all databases, but I'll look at that for a future revision. Thanks for the feedback.
February 26, 2016 at 2:38 pm
gaganlamba059 (2/26/2016)
Hi Steve,This is an excellen script. However, when I try to use this on SQL Server 2008 R2, It gives me an error:
"
Msg 207, Level 16, State 1, Line 63
Invalid column name 'authentication_type'.
"
The problem is that there is no column with that name in sys.database_principals in SQL 2008 R2. This column was added in later versions.
I'm not able to get the script updated on this submission any longer (pending approval for a new page), but a working version can be found here:
/*
This script will script the role members for all roles on the database.
This is useful for scripting permissions in a development environment before refreshing
development with a copy of production. This will allow us to easily ensure
development permissions are not lost during a prod to dev restoration.
URL for this script: http://www.sqlservercentral.com/scripts/Security/71562/ -- Periodically, updates are made to this script so check out the URL for updates.
Author: S. Kusen
Updates:
2015-08-21:
1. Modified section 3.1 to load to a temp table and populate different users based on an error in 2005/2008 because of the update made for contained databases. Thanks to Andrew G for pointing that out.
2. Altered section 4.1 to include COLLATE DATABASE_DEFAULT in the join statement. Thanks to Andrew G and PHXHoward for pointing that out.
2015-06-30:
1. Re-numbered all sections based on additional updates being added inline.
2. Added sections 8, 8.1; From Eddict, user defined types needed to be added.
3. Added sections 4, 4.1; From nhaberl, for orphaned users mapping (if logins don't exist, they will not be created by this script).
4. Updated section 3.1; From nhaberl, updated to include a default schema of dbo.
2014-07-25: Fix pointed out by virgo for where logins are mapped to users that are a different name. Changed ***+ ' FOR LOGIN ' + QUOTENAME([name]) +*** to ***+ ' FOR LOGIN ' + QUOTENAME(suser_sname([sid])) +***.
2014-01-24: Updated to account for 2012 contained db users
2012-05-14: Incorporated a fix pointed out by aruopna for Schema-level permissions.
2010-01-20:Turned statements into a cursor and then using print statements to make it easier to
copy/paste into a query window.
Added support for schema level permissions
Thanks to wsoranno@winona.edu and choffman for the recommendations.
*/
/*Prep statements*/
CREATE TABLE ##tbl_db_principals_statements (stmt varchar(max), result_order decimal(4,1))
IF ((SELECT SUBSTRING(convert(sysname, SERVERPROPERTY('productversion')), 1, charindex('.',convert(sysname, SERVERPROPERTY('productversion')))-1)) > 10)
EXEC ('
INSERT INTO ##tbl_db_principals_statements (stmt, result_order)
SELECT
CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '' + QUOTENAME([default_schema_name]) + SPACE(1) + '', SID = '' + CONVERT(varchar(1000), sid) + SPACE(1) + '' END; '')
ELSE (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' FOR LOGIN '' + QUOTENAME(suser_sname([sid])) + '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) + SPACE(1) + ''END; '')
END AS [-- SQL STATEMENTS --],
3.1 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_principals AS rm
WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */')
ELSE IF ((SELECT SUBSTRING(convert(sysname, SERVERPROPERTY('productversion')), 1, charindex('.',convert(sysname, SERVERPROPERTY('productversion')))-1)) IN (9,10))
EXEC ('
INSERT INTO ##tbl_db_principals_statements (stmt, result_order)
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(suser_sname([sid])) + '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) + SPACE(1) + ''END; '') AS [-- SQL STATEMENTS --],
3.1 AS [-- RESULT ORDER HOLDER --]
FROMsys.database_principals AS rm
WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */')
--SELECT * FROM ##tbl_db_principals_statements
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.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
[stmt],
3.1 AS [-- RESULT ORDER HOLDER --]
FROM##tbl_db_principals_statements
--WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
WHERE [stmt] IS NOT NULL
UNION
/*********************************************/
/********* MAP ORPHANED USERS *********/
/*********************************************/
SELECT '-- [-- ORPHANED USERS --] --' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT'ALTER USER [' + rm.name + '] WITH LOGIN = [' + rm.name + ']',
4.1 AS [-- RESULT ORDER HOLDER --]
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')
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 --],
5.1 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 [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 --],
7.1 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 --],
7.2 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
--WHEREusr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
/*********************************************/
/********* TYPE LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- TYPE 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(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 --],
8.1 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
UNION
SELECT '' AS [-- SQL STATEMENTS --],
9 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
10 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 --],
10.1 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].[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 --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
12 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 --],
12.1 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
DROP TABLE ##tbl_db_principals_statements
February 26, 2016 at 2:54 pm
Thank you very much!
Gaganpreet S Lamba
March 22, 2016 at 2:19 pm
For those still following this thread, the script was moved to http://www.sqlservercentral.com/scripts/login/138379/[/url] as this particular script/page could not be edited any longer. The admins couldn't figure out why, so I re-submitted with the most recent update, and should hopefully be able to edit this as fixes come up more frequently.
Thanks,
Steve
May 2, 2016 at 11:40 am
Steve,The script is really helpful.It runs perfect on SQL 2012, when I run it on SQL SERVER 2008 getting the below error. Can you help here, please.
Msg 207, Level 16, State 1, Line 64
Invalid column name 'authentication_type'.
May 5, 2016 at 9:05 am
sanjay s sarsambi (5/2/2016)
Steve,The script is really helpful.It runs perfect on SQL 2012, when I run it on SQL SERVER 2008 getting the below error. Can you help here, please.Msg 207, Level 16, State 1, Line 64
Invalid column name 'authentication_type'.
Check the update at http://www.sqlservercentral.com/scripts/login/138379/. That should work on 2008 as well without error.
October 20, 2016 at 5:57 am
First time using your script. Looks like db role members are scripted out but the role itself is not so the sp_addrolemember statements fail? I only had one role like that (in prod but not in dev oddly) that was easy enough to workaround. Thanks for your script.
August 4, 2017 at 2:05 pm
Got:
Msg 468, Level 16, State 9, Line 83
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
----
http://www.sqlservercentral.com/scripts/login/138379/ that solved above, thanks!
February 12, 2020 at 4:38 pm
The version 3 is not working in my SQL Server 10.0.2531.0 (2008)
Here's the error I get:
Msg 207, Level 16, State 1, Line 63
Invalid column name 'authentication_type'.
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy