Script DB Level Permissions v4.6

  • Can you provide a script to script out permissions for Azure SQL database, Thanks!

  • Can you add code to output Roles that are assigned permissions at the Database level?  It looks like I'm only seeing users in the output.  example:  I create a role named db_executor then assign that role at the db level to execute but it didn't show up in your output.

    I expected to see something like this:

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

    IF DATABASE_PRINCIPAL_ID('db_executor') IS NOT NULL GRANT EXECUTE TO [db_executor]

    Thanks! This is a great script.

  • I think this is the fix but please verify.  I added type of 'R' to your where statement in the select under DB LEVEL PERMISSIONS.

    WHERE [perm].[major_id] = 0

    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas

    AND [usr].[type] IN ('G', 'S', 'U', 'R') -- S = SQL user, U = Windows user, G = Windows group, R=Role  -- Added R for R types

  • Hi Kusen,

    Your script is awesome and very use full. But I need request one thing here. can we get below things when we execute this script?

    if not can add these things in your script?

    script out all any certificates in the database,

    Script out any users that are protected by a cert

    script out all user defined schemas,

    script out Application roles from the database,

    script out any nested role permissions,

    Thanks,

    Raju K.

    Raju K.

  • Great Script! Thanks for making this available.

    May I ask if there are plans to incorporate it into a stored procedure to allow e.g. the passing in of specific databases?

    Thanks

    Craig

  • I love this script!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  • Skript is amazing... Using it all the time...

    Does anyone know if the same exists for removing Users? I want to remove the users that left are in the Backupfile

  • Can you please suggest in running the same script on all databases in one go.

  • Updated to include dbo and drop existing users (commented out) to run in restored db.

    /*

    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:
    2020-07-07 v4.6: AG added database owner and fix database_principals that are named differently to AD (use latest name from AD/Windows, not SQL value)

    2018-11-06 v4.51: AG added ALTER USER as sp_revokeaccess fails due to a user owning a schema with tables. Drop fails and requires the SQL user to be remapped
    Added commented script section to allow dropping of all user permissions in restored database
    2017-07-10 v4.3:
    I was unable to easily get this into a stored procedure / powershell script, so this update includes the changes/updates noted here:
    1. Incorporated Andrew G's updates from previous feedback (Much delayed to being updated on the main script page). Thanks Andrew!
    2. danmeskel2002 recommended a fix for the SID issue for "SQL User without login".
    Changed this line:
    SID = '' + CONVERT(varchar(1000), sid)
    to
    SID = '' + CONVERT(varchar(1000), sid, 1)

    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

    print '
    /***************************************************************************/
    /***************************************************************************
    /* Delete existing users in database (so that they can be recreated with different permissions) */

    USE [' + DB_NAME() +']
    DECLARE @UserName nvarchar(256)
    DECLARE csrUser CURSOR FOR
    SELECT [name] FROM sys.database_principals WHERE principal_id >4 AND is_fixed_role <1 ORDER BY [name]

    OPEN csrUser FETCH NEXT FROM csrUser INTO @UserName WHILE @@FETCH_STATUS <> -1
    BEGIN
    BEGIN TRY
    EXEC sp_revokedbaccess @UserName
    END TRY
    BEGIN CATCH
    ROLLBACK
    END CATCH
    FETCH NEXT FROM csrUser INTO @UserName
    END

    CLOSE csrUser DEALLOCATE csrUser
    ***************************************************************************/
    /***************************************************************************/'


    /*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 SUSER_SNAME([sid]) FROM sys.database_principals WHERE SUSER_SNAME([sid]) = '' + SPACE(1) + '''''''' + SUSER_SNAME([sid]) + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '' + QUOTENAME([default_schema_name]) + SPACE(1) + '', SID = '' + CONVERT(varchar(1000), sid, 1) + SPACE(1) + '' END; '')
    ELSE
    CASE WHEN rm.name = ''dbo'' /* dbo "name" can be different to Windows User */
    THEN ''ALTER AUTHORIZATION ON DATABASE::'' + QUOTENAME(DB_NAME()) + '' TO '' + QUOTENAME(SUSER_SNAME([sid])) + '';''
    ELSE (''IF NOT EXISTS (SELECT SUSER_SNAME([sid]) FROM sys.database_principals WHERE SUSER_SNAME([sid]) = '' + SPACE(1) + '''''''' + SUSER_SNAME([sid]) + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' 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 ELSE ALTER USER '' + SPACE(1) + QUOTENAME(SUSER_SNAME([sid])) + '' WITH LOGIN = '' + QUOTENAME(SUSER_SNAME([sid])) + '';'')
    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''
    --AND name <> ''dbo'' /* include dbo database owner */')

    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 SUSER_SNAME([sid]) 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 ELSE ALTER USER '' + SPACE(1) + QUOTENAME([name]) + '' WITH LOGIN = '' + QUOTENAME(suser_sname([sid])) + '';'')
    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''
    AND NAME <> ''dbo''')

    --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
    AND [name] NOT IN ('public','dbo','guest','INFORMATION SCHEMA','sys')
    --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

    • This reply was modified 1 year ago by  Andrew G. Reason: Used Code block
  • i ve modified it so that

    -i get the results in a table before i restore my DB (modified a little the skript of S.Gusen)

    -restore the DB

    -then i change all owners of roles and schemas to dbo

    -i drop all the users

    -execute every line in the table so i get the old permissions

  • Thank you for the script. Very useful!

    One issue I have discovered is that the type level permissions syntax is incorrect. The script shows this:

    IF... GRANT EXECUTE ON [schemaName].[typeName] TO [roleName]

    But it should show this:

    IF... GRANT EXECUTE ON TYPE::[schemaName].[typeName] TO [roleName]

  • Great Script !!

    I added user type 'K' , because I found I have that type of user, also needing to be included.

    • This reply was modified 2 months, 1 week ago by  homebrew01.
  • Thank you very much for your script.

    Currently I am using a combination of other scripts to do what yours does. I have read in an entry that if you could make it work for all the databases of the entire instance.

    Could it be possible? I do, very often, the process of copying databases, from the same instance, from the production environment to the development environment. It would also help me get everyone on audit and security matters.

    Regards.

  • Hello Steve,

    Thanks for the great script, I use it on a daily schedule for various restore jobs but found a issue not sure its something to do with my logic or the code, below is the process I do in a sql job.

    Step 1 - Script out all db permissions using Script DB Level Permissions v4.5

    Step 2 - Restore database

    Step 3- Apply all the permissions back from the output of Script DB Level Permissions v4.5

    After restore if I check for any orphaned users there will be couple which is later synced or fixed in the next run of above 3 step job.

    e.g.

    Day 1 after restore orphaned users exist when I verify for them manually using

    sp_change_users_login 'report'

    Day 2 orphaned users will be appeared and will be synced with out put of Script DB Level Permissions v4.5(users will be appeared at [-- ORPHANED USERS --] --)

    Day 3- After restore few orphaned users are left again.

    I can add a step 4 to resync all orphaned users to make no orphaned users are left but wanted to check if its a known issue or something else.

    Thanks again for your time.

    Best.

  • I just added the "EXEC sp_change_users_login 'Update_One', 'login', 'user'" to the creating of the users in the script. If the user is already mapped it does not raise an error. So no problem

Viewing 15 posts - 16 through 30 (of 45 total)

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