May 26, 2021 at 9:54 pm
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.
May 27, 2021 at 7:34 am
I have at least 100 restore jobs, the pre-drop gonna help big time, thanks Andrew.
Cheers
May 27, 2021 at 11:32 am
what if the users own things?
June 3, 2021 at 4:53 pm
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!
June 10, 2021 at 6:08 pm
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.
June 10, 2021 at 6:22 pm
Thanks for the original script, and everyone's tips & improvements.
June 10, 2021 at 6:39 pm
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.
June 10, 2021 at 7:03 pm
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:
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.
June 10, 2021 at 7:10 pm
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.
June 11, 2021 at 2:56 pm
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.
June 11, 2021 at 3:04 pm
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.
June 11, 2021 at 3:06 pm
Comments posted to this topic are about the item Script DB Level Permissions v4.6
June 21, 2021 at 4:01 pm
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.
July 28, 2021 at 2:45 pm
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'')')
July 28, 2021 at 4:17 pm
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