General

Re-creating SQL Logins for standby databases

by Lewis · Thu 5th August, 2010

In preparation for an upcoming DR test I was aware of a limitation of using Standby/Read-only SQL databases that may be required to be brought online and immediately accessed by user accounts that didn’t exist on the DR database server.

When you configure log-shipping, the process essentially exports a copy of the database and imports it in to the target server in Standby/Read-only mode (this isn’t the only option, it’s just the one I use).

When you wish to bring that database online, either for the purposes of a DR test or because of an actual disaster, you execute:

RESTORE DATABASE [database] WITH RECOVERY;

1	RESTORE DATABASE [database] WITH RECOVERY;

Where database is the name of the database you wish to bring online.

This is great, up until the point that your application attempts to access the database. You will likely receive an access denied message, most likely because you haven’t created a duplicate user account on the DR database server, so you merrily create the account on the DR server and…you still get an access denied message.

Why? Well, when you created the new user, you probably used the GUI interface or a command like the following:

CREATE LOGIN [mylogin] WITH PASSWORD=N'mypassword',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;

CREATE LOGIN [mylogin] WITH PASSWORD=N'mypassword',

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF;

When this command executed or you click OK on the GUI pages, it creates the user account but in the background, the SQL server also generated a security id (SID) for the new user account.

The reason you cannot use the new user account to access the restored database is because the SID for the account you just created does not match the SID that is stored in the DR database ind despite having the same name and password, it is a completely different account as far as SQL Server is concerned. If you are in a DR scenario, you would probably just grant the new user account access to the database and everything would begin to work, but do you really want to be doing that in a DR scenario? I know I don’t.

If you are preparing your DR environment and your database is still in Standby/Read-only mode then you can’t grant the newly created user account access to the databases, so what do you do?

Thankfully, the answer is fairly straightforward because the CREATE LOGIN command in SQL Server 2005 allows you to specify the SID. Great..but what’s the SID of the original account?

Well, because we aren’t yet in a DR scenario (thankfully), we can identify the SID of the original account by executing a simple command on the active database server.

SELECT sid from dbo.syslogins WHERE name='mylogin';
Result: 0x417410CE04CDFB4296AF4AAAB4404348

1 2	SELECT sid from dbo.syslogins WHERE name='mylogin'; Result: 0x417410CE04CDFB4296AF4AAAB4404348

Now that we have the SID of the original account, we can create the new/shadow account on the DR server and specify the SID at the time of creation. You can only do this in the New Query window, the GUI doesn’t allow you to specify the SID.

CREATE LOGIN [mylogin] WITH PASSWORD=N'mypassword',
SID=0x417410CE04CDFB4296AF4AAAB4404348,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF

CREATE LOGIN [mylogin] WITH PASSWORD=N'mypassword',

SID=0x417410CE04CDFB4296AF4AAAB4404348,

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF

The account will be created and, because the SID of this new/shadow account matches to the SID in the Standby/Read-only database, when you do have a disaster, you don’t need to run any scripts or tinker with SQL user account permissions or your application configuration.

-Lewis