:unsure: Backup Particular DB Users and there Permissions.

  • 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

  • Take a look at this article and scripts from Warren Campbell:

    http://www.sqlservercentral.com/articles/Automation/76791/

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • 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]

  • 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