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 1 week, 6 days 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.

Viewing 13 posts - 31 through 43 (of 43 total)

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