June 4, 2013 at 6:48 pm
Hi Experts,
Is there any way to just script out users permissions in a given db. I know there is a script from Microsoft called SP_HELP_REVLOGIN which scripts out entire users at instance level but I am not looking for that or I am looking to use Object Explorer Details from which I can script out the users, I am looking for a script which gives me users and roles for just Database A.
My Stage,Dev & Test environments get refreshed very often so I have to take backup from production for database A and restore the database in Stage on Database A and same for other environments but when I am doing this Users and Roles of Production is coming with it and get restored on Stage and other environments so I have to drop them one by one and re-add Stage users and their permissions every time and it takes a lot of time. Is there any way I can only script out Database A Users and their Permissions and give them back. I searched on google but I couldn't find anything. Please let me know is there any way to do this.
Thank you for your help in advance
June 5, 2013 at 1:59 am
Take a look at this article and scripts from Warren Campbell:
http://www.sqlservercentral.com/articles/Automation/76791/
June 5, 2013 at 7:58 am
This script has always done the trick for me. It scripts out DB users, roles, object level permissions, and schema level permissions. It works on 2005 and 2008R2, but have not verified it on 2012.
/*
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.
Author: S. Kusen
Updates:
01/20/2010: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.
---------------------------------------------------------------------------------------------
Edited by: J. Goodwin - @SQL_JGood
12/20/2012: Added improvements to scripting of schema level permissions
---------------------------------------------------------------------------------------------
*/
--Execute script under the context of the Database for which you want to script out security
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
/*********************************************
**********CREATE Users**************
**********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --]
,2 AS [-- RESULT ORDER HOLDER --]
UNION
select 'CREATE USER ['+ name +'] FOR LOGIN ['+ name +']' as [-- SQL STATEMENTS --]
,2 AS [-- RESULT ORDER HOLDER --]
from sys.database_principals
where type = 'S'
and name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
UNION
SELECT '' AS [-- SQL STATEMENTS --]
,3 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --]
,3 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 --]
,3 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 --]
,4 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
,5 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 --]
,5 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
SELECT '' AS [-- SQL STATEMENTS --]
,6 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
,7 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 --]
,7 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
--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 --]
,8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
,9 AS [-- RESULT ORDER HOLDER --]
UNION
--select * from sys.database_permissions
--select * from sys.database_principals
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(PERM.major_id)) + ' TO' + SPACE(1) + '[' + 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 --]
,10 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 class = 3 -- 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
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 10, 2013 at 10:47 am
Thank you J Good & Hanshi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply