Script DB Level Permissions v4.6

Question

Script DB Level Permissions v4.6

S. Kusen

SSChampion

Points: 10936
More actions
June 11, 2021 at 3:06 pm

#316489

Comments posted to this topic are about the item Script DB Level Permissions v4.6

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

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

Andrew G SSChampion Points: 12809 More actions · Answer 1

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.

rao2506 Valued Member Points: 59 More actions · Answer 2

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

Cheers

phoinix Newbie Points: 7 More actions · Answer 3

phoinix

Newbie

Points: 7

May 27, 2021 at 11:32 am

#3888693

what if the users own things?

Dromero22 Newbie Points: 2 More actions · Answer 4

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!

S. Kusen SSChampion Points: 10936 More actions · Answer 5

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.

homebrew01 SSC Guru Points: 55539 More actions · Answer 6

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

S. Kusen SSChampion Points: 10936 More actions · Answer 7

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.

S. Kusen SSChampion Points: 10936 More actions · Answer 8

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:

PREP STATEMENTS: WHERE [type] IN (''U'', ''S'', ''G'',''E'',''X'')
[-- DB LEVEL PERMISSIONS --]: AND [usr].[type] IN ('G', 'S', 'U', 'R',E','X') -- S = SQL user, U = Windows user, G = Windows group
MAP ORPHANED USERS: WHERE rm.[type] IN ('U', 'S', 'G','E','X')
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.

S. Kusen SSChampion Points: 10936 More actions · Answer 9

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.

Dromero22 Newbie Points: 2 More actions · Answer 10

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.

homebrew01 SSC Guru Points: 55539 More actions · Answer 11

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 2 years, 10 months ago by homebrew01.

EvilMonkey Newbie Points: 2 More actions · Answer 12

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.

phoinix Newbie Points: 7 More actions · Answer 13

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

S. Kusen SSChampion Points: 10936 More actions · Answer 14

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.