Script DB Level Permissions

  • Mubeen Mohammed-203067 (3/13/2015)


    Hi Kusen,

    First, Thanks for the excellent script and keep updating with the feedback is enourmous.

    I just tested the script by adding 'R' in two stmts (as you mentioned in one of the post) but still I think it is not creating the User DB Roles as expected.

    I would like to see these roles should be scripted as below. I do not have these roles in Production as DEV but ultimately after restoring/overwriting the existing DEV database with a copy of Production, I need to re-create these roles and then add required members into that and the object level permissions.

    Example: CREATE ROLE [DevUsersOnly] AUTHORIZATION [dbo]

    Thanks,

    Mubeen

    The script would need the role to exist, and simply will script out adding the users to that role, as it currently exists. I see your need, but I don't really know how common a situation that is. I would think that most either promote or refresh the databases between environments and keep the roles in both, just not have any users in those roles in produciton. I'll see what I can do to look at user-defined roles and re-creating them if they don't exist before scripting out the addition of users to those roles. Thanks, as always, for the feedback.

  • krishna85 (3/27/2015)


    Script is really good and it helps a lot.

    Does this script resolves the issues with Orphan Users?

    If not it would be good if we include that portion in the script as well....

    When you say "resolves", do you mean delete orphaned users? If so, that is not really what this script is designed to do. It is designed to script out permissions within a database to help with restoring those permissions, generally when a database is pulled down from production to a non-production environment where different permissions will be established. If a database user is not mapped to a login, there multiple reasons why a user may exist without a login. Here are two of them: 1) Users that exist in contained databases that will not be assigned with a login, and 2) users created "WITHOUT LOGIN".

  • Thank you!

    After the database refresh, when i re-apply the permissions extracted before the refresh, I get complains from the users about connectivity issues.

    This is because the SID's of the logins don't match between our two environments.

    I know, I can re-create those logins and match the SID's so I don't run into this again.

    Adding the orphan users fix piece to the script would be helpful.... However, I understand that is not the purpose the script was designed for....

    Will get this fixed from my end.....Thanks again for this wonderful script!

  • hi Steve,

    thanks for this awesome script, very useful!

    would it be possible to add User-Defined Table Types as well? those are the only permissions now i'm missing.

    see StackExchange for the example query to output the permissions.

    many thanks,

    regards Eddict

  • hi Steve,

    another request;

    when I grant View Definition to a role on schema level it is shown by the script, but if I grant it on database level it doesn't.

    could you please add this as well?

    shown in 'DB LEVEL SCHEMA PERMISSIONS' section;GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [myRole]

    expected, but not shown (in 'DB LEVEL PERMISSIONS' section?);GRANT VIEW DEFINITION ON DATABASE::[MyDB] TO [myRole]

    regards Eddict

  • Eddict (5/11/2015)


    hi Steve,

    another request;

    when I grant View Definition to a role on schema level it is shown by the script, but if I grant it on database level it doesn't.

    could you please add this as well?

    shown in 'DB LEVEL SCHEMA PERMISSIONS' section;GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [myRole]

    expected, but not shown (in 'DB LEVEL PERMISSIONS' section?);GRANT VIEW DEFINITION ON DATABASE::[MyDB] TO [myRole]

    regards Eddict

    stupid me, found it myself. in the 'DB LEVEL PERMISSIONS' just add 'R' as allowed type;

    AND [usr].[type] IN ('G', 'S', 'U', 'R') -- S = SQL user, U = Windows user, G = Windows group, R = Database role

  • Eddict (5/4/2015)


    hi Steve,

    thanks for this awesome script, very useful!

    would it be possible to add User-Defined Table Types as well? those are the only permissions now i'm missing.

    see StackExchange for the example query to output the permissions.

    many thanks,

    regards Eddict

    added it myself;

    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

  • Eddict (5/11/2015)


    Eddict (5/4/2015)


    hi Steve,

    thanks for this awesome script, very useful!

    would it be possible to add User-Defined Table Types as well? those are the only permissions now i'm missing.

    see StackExchange for the example query to output the permissions.

    many thanks,

    regards Eddict

    added it myself;

    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

    Many thanks, Eddict. I apologize that I didn't have the time available to review this myself. I'll get this updated on the main form as well and be sure to give you kudos, unless you don't want your name showing in the main script. I like to give credit where credit is due.

    Regards,

    Steve

  • S. Kusen (5/11/2015)


    Many thanks, Eddict. I apologize that I didn't have the time available to review this myself. I'll get this updated on the main form as well and be sure to give you kudos, unless you don't want your name showing in the main script. I like to give credit where credit is due.

    Regards,

    Steve

    hi Steve, no problem! you might want to review my 2 additions and adjust it wherever needed.

    regards Eddict

  • Hey, guess this should work for mapping orphaned users back to their logins but doesn't create missing logins. (Because it's not on the db level anymore)

    This works from 2k5SP2 otherwise it won't. See this post

    /*********************************************/

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

    FROMsys.database_principals AS rm

    Inner JOIN sys.server_principals as sp

    ON rm.name = sp.name 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

    Furthermore I've changed the user creation script - if there was no default schema set there were no rows returned so as a fallback I've put dbo as the default schema if it is missing. Also used the respective LOGIN name for the FOR LOGIN clause in the create user statement if there a different names used.

    /*********************************************/

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

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

  • Thanks I've found this script very useful.

    However the last update for contained databases seems to have broken the script for SQL2005 & SQL2008.

    Line 63 has a reference to column "authentication_type" in sys.database_principals , which does not exist in the older versions of SQL.

    Msg 207, Level 16, State 1, Line 63

    Invalid column name 'authentication_type'.

    I'm not sure how to get around the issue other than commenting out the contained database code.

  • I also get a collation conflict on line 85 for databases like sharepoint which have a different collation to server default.

    I've found it works fine when changing line 85 from

    ON rm.name = sp.name and rm.sid <> sp.sid

    to

    ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sid

  • Thanks for the feedback. I will review and get an update posted.

  • Thanks for making this script. It looks really nice.

    V3 that is posted no longer includes the source script url in the comments section of the script. (http://www.sqlservercentral.com/scripts/Security/71562/)

    Also, I had to add COLLATE DATABASE_DEFAULT to the map orphaned users section on one of my databases.

    ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sid

    Howard

  • Hello and thanks for the script. I need to modify the script so that it only returns results for a single login/user. Has anyone done this yet? I have to grant permissions to another login/user that is based on the permissions of an existing login/user. being able to do this would be very helpful as I have frequently get this type of request. sifting through the results for a single login/user is a little difficult on some of our larger systems.

    thanks,

    -d

Viewing 15 posts - 31 through 45 (of 58 total)

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