Script DB Level Permissions v4.6

  • Just drop all users after restore (see the commented part of my last reply here for a script to do this). They have been scripted out anyway.  When you recreate the users will remap to the existing server logins on the new server.

  • I have at least 100 restore jobs, the pre-drop gonna help big time, thanks Andrew.

    Cheers

  • what if the users own things?

  • Hi Steve,

    Are there additional changes to the script if it will be ran on an Azure SQL Managed Instance aside from adding the type 'E' and 'X' (E = External user, X = External group)?  Thanks!

  • Hey all.  There have been quite a few comments recently, and some edits provided by Andrew G last year (I'm a bit behind, eh?).  I am reviewing these and will try to incorporate them into the script, and will attempt to the update published on the existing link.  Appreciate that everyone is getting good use out of the script.

  • Thanks for the original script, and everyone's tips & improvements.

  • nani_varaprasad wrote:

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

    Unfortunately, this isn't designed to run that way since it is generating print statements.  It would take a bit of work to convert over to SELECT statements, or to wrap it up into a procedure and test (there are A LOT of single quotes in this script to escape).  I'll see if I can give it a shot in the future, but the use case hasn't been prevalent enough for my day-to-day use to warrant updating it.  Happy to take suggestive edits if you're willing to give it a shot.

  • Dromero22 wrote:

    Hi Steve,

    Are there additional changes to the script if it will be ran on an Azure SQL Managed Instance aside from adding the type 'E' and 'X' (E = External user, X = External group)?  Thanks!

     

    Good question- I haven't done much with managing permissions on databases stored on MI's in Azure.  Can you let me know where you placed the E and X?  I am guessing for:

    1. PREP STATEMENTS:  WHERE [type] IN (''U'', ''S'', ''G'',''E'',''X'')
    2. [-- DB LEVEL PERMISSIONS --]: AND [usr].[type] IN ('G', 'S', 'U', 'R',E','X') -- S = SQL user, U = Windows user, G = Windows group
    3. MAP ORPHANED USERS: WHERE rm.[type] IN ('U', 'S', 'G','E','X')
    4. DB ROLE PERMISSIONS: and [type] IN ('G', 'S', 'U', 'E','X')

    I don't currently have an MI to use to test, so it would be cool to have you try to edit and run for us.

  • Fran4mat wrote:

    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]

    Thanks for sharing.  Will get that edited and hopefully published soon.

  • Thanks Steve! Yup, those are the 4 sections where I added types 'E' and 'X' and it did capture the SQL MI users too. On a side note, I have to run the script again after the database restore just to get the orphaned users.  Your script was really great! Thank you for sharing it to everyone.

  • In 1 database, I have a user type 'K'

    K - ASYMMETRIC_KEY_MAPPED_USER

    I don't know much about it. Not sure if I will need to migrate it to a new server project coming up.

    • This reply was modified 3 years, 3 months ago by  homebrew01.
  • Comments posted to this topic are about the item Script DB Level Permissions v4.6

  • Love the script, very helpful for restores, but I am trying to edit it to work with just one account that I need to replace throughout many servers. I wonder if this was set up before as I saw "@olduser" listed in a where statement.

  • i added the update one for users that have a different Id than the login on the server.

     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, 1) + 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;exec sp_change_users_login ''''Update_one'''' , ''''''+ [name]+'''''', '''''' + suser_sname([sid]) + ''''''; '')
    END AS [-- SQL STATEMENTS --],
    3.1 AS [-- RESULT ORDER HOLDER --]
    FROM sys.database_principals AS rm
    WHERE [type] IN (''S'') /* windows users, sql users, windows groups */
    AND NAME NOT IN (''guest'',''dbo'')')
  • phoinix wrote:

    i added the update one for users that have a different Id than the login on the server.

     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, 1) + 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;exec sp_change_users_login ''''Update_one'''' , ''''''+ [name]+'''''', '''''' + suser_sname([sid]) + ''''''; '')
    END AS [-- SQL STATEMENTS --],
    3.1 AS [-- RESULT ORDER HOLDER --]
    FROM sys.database_principals AS rm
    WHERE [type] IN (''S'') /* windows users, sql users, windows groups */
    AND NAME NOT IN (''guest'',''dbo'')')

    I can understand why this would be useful, but wouldn't it make more sense to ensure that the login SID's are already synced up?  This doesn't hurt to have in there, so I'll look at getting the script updated, but it would make more sense to sync the login SID's to me, if you're consistently restoring from one environment to another, presumably where the logins of the same name have the same password.

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

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