0

I have set up log shipping, but I don't see how I can reset orphaned SQL Server credentials on the destination server as databases are in standby/read-only mode. I am getting push-back on implementing this functionality. In light of this, I would like to make it as easy as possible for developers to use the log shipping databases for reporting as opposed to the live transactional databases.

I would prefer to use Windows credentials which wouldn't have to be reset, but wouldn't that somehow require all users (of reports) to be validated ?

0

1 Answer 1

1

The trick is to avoid the orphaned logins in the first place, not to synchronize them afterwards.

As you probably know, 'orphaned' users are due to the SID associated with the user in the database not matching the SID associated with the login on the server. When a login is created on a server, the value for the SID is either taken from Active Directory (if you are using "integrated" or "windows" logins) or generated by SQL Server (in the case of SQL logins). With SQL Server logins, the value of the SID is generated by SQL Server. The value will vary each time you create a login [hint] unless you take special care.

IOW, if you create a login named "foo" on two servers, each login will have a different SID value. If you create a login named "foo", drop it and create it again, each occurrence of "foo" will have a different SID value.

Strategy #1: use integrated security

With Integrated security, the SID used by SQL Server for the login on the server and the user in the database is taken from the Active Directory login. Because of that, the values can't differ. Changing from SQL logins to integrated logins is usually a hassle, developers generally don't like to spend time on infrastructure work and it's probably a non-starter.

Strategy #2: use the "SID" parameter when creating logins on the destination server.

If you create a login using TSQL code, you can specify a specific value for a SID. You can construct a set of CREATE LOGIN statements (including SIDs and exported passwords) so that the SIDs match on both servers. That way, when you restore the database (via shipped log backups or any other way), the SIDs will match and people can get to the data.

Microsoft provides code that will help you create these CREATE LOGIN statements based on what exists in the source server. You can then copypaste those statements over to the destination server and run them. The name of the procedure is sp_help_revlogin. The code varies slightly between SQL Server versions, but the version for SQL2008R2 is available from Microsoft here

TLDR: I'd recommend using sp_help_revlogin on the source server to create new CREATE LOGIN statements for the logins that have to sync up. (You don't need to do every login, just the ones that have problems.) Then, use those statements to drop and recreate the logins on the destination server. Since you will be dropping logins, you should be aware of any database permissions that those logins already have (maybe there are other databases on the destination server that the developers already have permissions in?) and you should be able to recreate those permissions after you get the login recreated.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.