Script DB Level Permissions

  • dbrejnak

    Newbie

    Points: 5

    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; ')

  • nhaberl

    SSC Veteran

    Points: 251

    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

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

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

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

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

    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

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

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

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

    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

  • saikamesh.g

    Newbie

    Points: 3

    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

  • salil.dixit1984

    Newbie

    Points: 7

    Can we use this script with 'sp_msforeachdb' ? so that we can get a output for all databases on instance level ?

  • gaganlamba059

    SSC Journeyman

    Points: 95

    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.

  • S. Kusen

    SSChampion

    Points: 10859

    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.

  • S. Kusen

    SSChampion

    Points: 10859

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

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

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

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

    )

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

    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

    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

    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

  • gaganlamba059

    SSC Journeyman

    Points: 95

    Thank you very much!

  • S. Kusen

    SSChampion

    Points: 10859

    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

  • sanjay s sarsambi

    SSC Rookie

    Points: 41

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

  • S. Kusen

    SSChampion

    Points: 10859

    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.

  • Jeff Burton

    Default port

    Points: 1499

    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.

  • SQLRocker

    SSCrazy

    Points: 2087

    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!

Viewing 13 posts - 46 through 58 (of 58 total)

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