SQLTeam.com | Weblogs | Forums

SQL Login transfers


#1

does anyone have a better or more complete to keep original login security info from a database (dev) that is going to be refreshed from production, thus logins can be reinstated?
Looking for sort of stand-alone script that does not need 'human' intervention... as in copy the results of the queries from sys.logins (SQL & Windows) ?

Thanks!!


#2

Logins are set at the server level and wouldn't change with a refresh of the database. If the logins are set as Windows logins/groups - and those accounts are added to the production database (without a login), then they will synch automatically when the database is restored to dev.

If the logins are SQL logins - and you created the user in the production database without a login but with the same SID as the login on the dev system, then the user will automatically synch when the database is restored to dev.

If you cannot add the users to production - then you need to create a script that adds the user to the database after restore to the dev environment. Once that script is created it can be applied to dev every time the database is restored - based on the logins defined on the dev system.

If you are constantly having to remove the user from the database in dev - and then re-add the user for the same login, then the problem is because the login SID on dev does not match the login SID from production. This can be fixed by recreating the login on dev to have the same SID as the login in production - this will eliminate any requirements for scripting to update the user in the database(s).


#3

Hi Jeff,
Actually the recurrent case is that the business requires frequent refreshes from the Prod DB to different Non-Prod databases.
What has been happening is that for each 'previous' refresh they (previous DBAs) have hard coded the database users in a T-SQL job (step) with lesser /different permissions and even database users.
What I'm trying to accomplish is to create a generic 'step' that I can add to a sched-job so ending with this nightmare of hardcoding user account info pre-refresh and post refresh.
They have modified a sproc from Brent Ozar which basically takes the latest Prod backup and restores it onto a Non-Prod database thus wiping out the previous database user account info (server level info. remains) but then after the restore and some business post T-SQL steps we have to recreate the user accounts.
Users are already tired of loosing their privileges; this is a fairly new deployment to Prod.
I just like to create a "step" to execute before the restore and re-apply after the business post steps.

I used to use a smaller version of the MS ...rev_login... sproc but that was when these refreshes were not so often requested (in other companies). Also read something about a Powershell function but in my own test environment this Powershell DBAtools are not installing (Windows 7 & SQL 2014, vs. Windows server VMware 2016 and SQL 2016)

Thanks for the reply Jeff!!

lec.


#4

It is a bit of a cheat but if you collect the SID's from the Logins on your Prod Server and create corresponding SID's on the non-Prod server whenever you refresh your database the DB users will sync with the Server logins.

You can do this when you script the creation of your logins.

To get the SID's, SELECT name, SID from sys.syslogins;

To script the creation at the other end

CREATE LOGIN [blah] WITH PASSWORD = N'some password', SID=0xblahblah; You can add other options in there as well like default database, language, expiry etc.

You would only need to do this once and there woudl be no extra steps in your refresh scripts.

You would however need to keep up to date in what logins ou have in Prod as nothing would get done automatically.


#5

Not sure why you would create a user in prod - then recreate that user in dev with different permissions. You would want to create the users with the same permissions to insure that any testing/validation will act the same and not fail when moved to production.

Either way - the key is to insure that the SQL logins on each instance have the same SID. If they have the same SID then you don't have to run anything to fix the user - that is you don't have to run sp_change_users_login to link the login to the user.

If you need to adjust the permissions for a user then you should try to figure out what rule is being applied. Once you have that defined you can then automate that process against the set of logins/users on that instance.

The problem you will run into with any script - is the fact that the user already exists in the restored database. This will require dropping and recreating the user every time which isn't necessary if the SIDs match up - the only thing that is necessary at that point is to apply the defined rules for that system.