Script DB Level Permissions v4.5

  • Andrew G

    SSChampion

    Points: 12687

    Thanks S. Kusen, great script.

    You might also want to add SET NOCOUNT ON and update the URL in the comments to this new V4 article.

  • sar99

    Old Hand

    Points: 321

    Thanks for the script, you may add the CREATE ROLE as well for user defined roles before you add the role member

  • S. Kusen

    SSChampion

    Points: 10849

    Andrew G (3/22/2016)


    Thanks S. Kusen, great script.

    You might also want to add SET NOCOUNT ON and update the URL in the comments to this new V4 article.

    Thanks for the feedback. Will look at getting the NOCOUNT added, and I commented over on the other thread. I couldn't get that posted up over there until this was posted and I had the new URL.

    Steve

  • S. Kusen

    SSChampion

    Points: 10849

    sar99 (3/22/2016)


    Thanks for the script, you may add the CREATE ROLE as well for user defined roles before you add the role member

    Good idea, thanks for the suggestion. Will get that changed in a future revision.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • nhaberl

    SSC Veteran

    Points: 251

    Awesome script, gets even perfect!

    I've done some slight modifications and added optional support for scripting a specific dbuser - see @DB_USER.

    If left empty it's executed for all users in the db.

    Therefore I moved the Prep Statement after declarations.

    I also added NOCOUNT ON for better xcopy experience.

    Hope it's helpful for anybody!

    SET NOCOUNT ON

    DECLARE

    @sql VARCHAR(2048)

    ,@sort INT

    ,@DB_USER VARCHAR(128)

    SET @DB_USER = ''

    /*Prep statements*/

    IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_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 --]

    FROM sys.database_principals AS rm

    WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */

    AND (rm.name = ''' + @DB_USER + ''' OR ''' + @DB_USER + ''' = '''')'

    )

    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 --]

    FROM sys.database_principals AS rm

    WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */

    AND (rm.name = ''' + @DB_USER + ''' OR ''' + @DB_USER + ''' = '''')'

    )

    -- SELECT * FROM ##tbl_db_principals_statements

    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 --]

    FROM sys.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 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 --]

    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

    AND (name = @DB_USER OR @DB_USER = '')

    )

    --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

    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 --],

    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

    WHERE (usr.name = @DB_USER OR @DB_USER = '')

    --WHERE usr.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

    WHERE (usr.name = @DB_USER OR @DB_USER = '')

    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

    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 --],

    10.1 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

    AND (usr.name = @DB_USER OR @DB_USER = '')

    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

    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 --],

    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

    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

    SET NOCOUNT OFF

  • SQLPRODDBA

    Say Hey Kid

    Points: 706

    Adding IF NOT EXISTS for users and roles and IF EXISTS while granting rights would be a great add on. So while executing the script out it won't fail.

  • Andrew G

    SSChampion

    Points: 12687

    Hi Steve, I use your script so much so I've got an update for you which includes some of the suggestions from the comments.

    My main problem was when scripting out permissions from master, extended stored procs were not getting picked up, so I've updated to include that.

    /*

    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/login/138379/

    Old URL 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:

    2016-10-31: AG 1. Added extended stored procedures and system object permissions for master database in OBJECT LEVEL PERMISSIONS area by removing join to sys.objects and using functions instead

    2. Added EXISTS check to all statements

    3. Added CREATE ROLE before adding principals to roles

    2016-08-25: AG 1. Remove default database being specified for an AD group user as this option causes a failure on create

    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.

    */

    SET NOCOUNT ON

    /*Prep statements*/

    IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_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])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '')

    END AS [-- SQL STATEMENTS --],

    3.1 AS [-- RESULT ORDER HOLDER --]

    FROM sys.database_principals AS rm

    WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */

    AND NAME <> ''guest''')

    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])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '') AS [-- SQL STATEMENTS --],

    3.1 AS [-- RESULT ORDER HOLDER --]

    FROM sys.database_principals AS rm

    WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */

    AND NAME <> ''guest''')

    --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 --]

    FROM sys.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 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME([name],'''') COLLATE database_default + ') IS NULL' + SPACE(1) + 'CREATE ROLE'

    + SPACE(1) + QUOTENAME([name]),

    5.1 AS [-- RESULT ORDER HOLDER --]

    FROM sys.database_principals

    WHERE [type] ='R' -- R = Role

    AND [is_fixed_role] = 0

    --ORDER BY [name] ASC

    UNION

    SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(rm.member_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + 'EXEC sp_addrolemember @rolename ='

    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') COLLATE database_default + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') COLLATE database_default AS [-- SQL STATEMENTS --],

    5.2 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 --],

    7.1 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

    CASE

    WHEN perm.state <> 'W' THEN perm.state_desc

    ELSE 'GRANT'

    END

    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(perm.major_id)) + '.' + QUOTENAME(OBJECT_NAME(perm.major_id)) --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

    /* No join to sys.objects as it excludes system objects such as extended stored procedures */

    /* 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 /* Include System objects when scripting permissions for master, exclude elsewhere */

    ( DB_NAME() <> 'master' AND perm.major_id IN (SELECT [object_id] FROM sys.objects WHERE type NOT IN ('S'))

    OR DB_NAME() = 'master'

    )

    --WHERE usr.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 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

    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

    SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

    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 --],

    10.1 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 --],

    11 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

    12 AS [-- RESULT ORDER HOLDER --]

    UNION

    SELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(grantee_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

    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 --],

    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

    IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_statements

  • Charlie Daw

    SSC Rookie

    Points: 33

    Great script. One note. In the V4 script, the URL included in the comments points to the V3 script.[font="Courier New"][/font]

  • danmeskel2002

    SSC Rookie

    Points: 35

    S. Kusen - Thursday, March 3, 2016 9:20 AM

    Comments posted to this topic are about the item Script DB Level Permissions v4

    Hi Steve - the script is so handy and thank you very much for making it available to people like me... One thing that I had issue was converting the SID especially for CDC that creates USERS WITHOUT login... so I modified this part,  SID = '' + CONVERT(varchar(1000), sid) to  SID = '' + CONVERT(varchar(1000), sid, 1). I do not know if it matters but I wanted to jut let you know...

    Thanks,
    D.

  • SQLQuest29

    SSCrazy Eights

    Points: 8203

    Andrew G - Monday, October 31, 2016 12:18 AM

    Hi Steve, I use your script so much so I've got an update for you which includes some of the suggestions from the comments.My main problem was when scripting out permissions from master, extended stored procs were not getting picked up, so I've updated to include that./*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/login/138379/ Old URL http://www.sqlservercentral.com/scripts/Security/71562/ -- Periodically, updates are made to this script so check out the URL for updates. Author: S. KusenUpdates:2016-10-31: AG 1. Added extended stored procedures and system object permissions for master database in OBJECT LEVEL PERMISSIONS area by removing join to sys.objects and using functions instead 2. Added EXISTS check to all statements 3. Added CREATE ROLE before adding principals to roles 2016-08-25: AG 1. Remove default database being specified for an AD group user as this option causes a failure on create2015-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 users2012-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 permissionsThanks to wsoranno@winona.edu and choffman for the recommendations.*/SET NOCOUNT ON/*Prep statements*/IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_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])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '') END AS [-- SQL STATEMENTS --], 3.1 AS [-- RESULT ORDER HOLDER --] FROM sys.database_principals AS rm WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */ AND NAME <> ''guest''')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])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '') AS [-- SQL STATEMENTS --], 3.1 AS [-- RESULT ORDER HOLDER --] FROM sys.database_principals AS rm WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */ AND NAME <> ''guest''')--SELECT * FROM ##tbl_db_principals_statementsDECLARE @sql VARCHAR(2048) ,@sort INT DECLARE tmp CURSOR FOR/*********************************************//********* DB CONTEXT STATEMENT *********//*********************************************/SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --], 1 AS [-- RESULT ORDER HOLDER --]UNIONSELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --], 1.1 AS [-- RESULT ORDER HOLDER --]UNIONSELECT '' 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 NULLUNION/*********************************************//********* MAP ORPHANED USERS *********//*********************************************/SELECT '-- [-- ORPHANED USERS --] --' AS [-- SQL STATEMENTS --], 4 AS [-- RESULT ORDER HOLDER --]UNIONSELECT 'ALTER USER [' + rm.name + '] WITH LOGIN = [' + rm.name + ']', 4.1 AS [-- RESULT ORDER HOLDER --]FROM sys.database_principals AS rm Inner JOIN sys.server_principals as sp ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sidWHERE 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 --]UNIONSELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME([name],'''') COLLATE database_default + ') IS NULL' + SPACE(1) + 'CREATE ROLE' + SPACE(1) + QUOTENAME([name]), 5.1 AS [-- RESULT ORDER HOLDER --]FROM sys.database_principalsWHERE [type] ='R' -- R = Role AND [is_fixed_role] = 0--ORDER BY [name] ASCUNIONSELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(rm.member_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') COLLATE database_default + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') COLLATE database_default AS [-- SQL STATEMENTS --], 5.2 AS [-- RESULT ORDER HOLDER --]FROM sys.database_role_members AS rmWHERE 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 ASCUNIONSELECT '' 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 --]UNIONSELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(perm.major_id)) + '.' + QUOTENAME(OBJECT_NAME(perm.major_id)) --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 /* No join to sys.objects as it excludes system objects such as extended stored procedures */ /* 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 /* Include System objects when scripting permissions for master, exclude elsewhere */ ( DB_NAME() <> 'master' AND perm.major_id IN (SELECT [object_id] FROM sys.objects WHERE type NOT IN ('S')) OR DB_NAME() = 'master' ) --WHERE usr.name = @OldUser--ORDER BY perm.permission_name ASC, perm.state_desc ASCUNION/*********************************************//********* TYPE LEVEL PERMISSIONS *********//*********************************************/SELECT '-- [-- TYPE LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --], 8 AS [-- RESULT ORDER HOLDER --]UNIONSELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + 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_idUNIONSELECT '' AS [-- SQL STATEMENTS --], 9 AS [-- RESULT ORDER HOLDER --]UNION/*********************************************//********* DB LEVEL PERMISSIONS *********//*********************************************/SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --], 10 AS [-- RESULT ORDER HOLDER --]UNIONSELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + 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 --], 10.1 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 = @OldUserWHERE [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 groupUNIONSELECT '' AS [-- SQL STATEMENTS --], 11 AS [-- RESULT ORDER HOLDER --]UNION SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --], 12 AS [-- RESULT ORDER HOLDER --]UNIONSELECT 'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(grantee_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + 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 --], 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_idWHERE class = 3 --class 3 = schemaORDER BY [-- RESULT ORDER HOLDER --]OPEN tmpFETCH NEXT FROM tmp INTO @sql, @sortWHILE @@FETCH_STATUS = 0BEGIN PRINT @sql FETCH NEXT FROM tmp INTO @sql, @sort ENDCLOSE tmpDEALLOCATE tmp IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_statements

    Would be good if this was created as an SP and then db name as variable or ALL for all dbs. This is very useful script .. thanks a lot for that !

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • S. Kusen

    SSChampion

    Points: 10849

    My apologies to those replying and leaving feedback for this script.  I am reviewing them and will get an update posted with the provided enhancements.  Also, many thanks for some of the kind words here.  Hope to have the updates included and posted in the next few weeks.
    SK

  • bshimonov

    SSC Veteran

    Points: 298

    Great script, but, I think, 'dbo' need to be excluded from create user statement.


    Boris

  • inayatkhan

    Mr or Mrs. 500

    Points: 555

    one issue its create the role but not grant the permission like I create ROLE SP_EXECUTE and grant execute permission to this role. 

    When I use this script its create the role but didn't give the execute permission.

  • S. Kusen

    SSChampion

    Points: 10849

    inayatkhan - Tuesday, May 15, 2018 7:14 PM

    one issue its create the role but not grant the permission like I create ROLE SP_EXECUTE and grant execute permission to this role. 

    When I use this script its create the role but didn't give the execute permission.

    Hi, Can you provide a little more context around this?  I tried to re-create this scenario, but didn't see the same issue that you did:
    1. Created a TestDB database.
    2. Created a dummy table.
    3. Created a procedure to select from the dummy table.
    4. Created a role.
    5. Granted execute permissions on the procedure to the role.

    When I ran the script on a SQL 2012 SP4 instance, this was my output -- I put the text in bold where it seemed to create the execute permissions on the role for the objects specified:
    -- [-- DB CONTEXT --] --
    USE [TestDB]

    -- [-- DB USERS --] --
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = 'test_role_member') BEGIN CREATE USER [test_role_member] FOR LOGIN [test_role_member] WITH DEFAULT_SCHEMA = [dbo] END;
    -- [-- ORPHANED USERS --] --
    -- [-- DB ROLES --] --
    IF DATABASE_PRINCIPAL_ID('public') IS NULL CREATE ROLE [public]
    IF DATABASE_PRINCIPAL_ID('test_role_permissions') IS NULL CREATE ROLE [test_role_permissions]
    IF DATABASE_PRINCIPAL_ID('test_role_member') IS NOT NULL EXEC sp_addrolemember @rolename = 'test_role_permissions', @membername = 'test_role_member'

    -- [-- OBJECT LEVEL PERMISSIONS --] --
    IF DATABASE_PRINCIPAL_ID('test_role_permissions') IS NOT NULL GRANT EXECUTE ON [dbo].[usp_select_from_DummyTable] TO [test_role_permissions]
    IF DATABASE_PRINCIPAL_ID('test_role_permissions') IS NOT NULL GRANT SELECT ON [dbo].[DummyTable] TO [test_role_permissions]
    -- [-- TYPE LEVEL PERMISSIONS --] --

    -- [--DB LEVEL PERMISSIONS --] --
    IF DATABASE_PRINCIPAL_ID('test_role_member') IS NOT NULL DENY ALTER ANY DATABASE DDL TRIGGER TO [test_role_member]
    IF DATABASE_PRINCIPAL_ID('test_role_member') IS NOT NULL GRANT CONNECT TO [test_role_member]
    IF DATABASE_PRINCIPAL_ID('test_role_member') IS NOT NULL GRANT VIEW DEFINITION TO [test_role_member]

    -- [--DB LEVEL SCHEMA PERMISSIONS --] --

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply