This procedure also applies to a standby database in log-shipping configurations. The standby database is read-only and cannot be configured with any security/login information. However, this procedure permits access to the standby database.
Steps to reproduce the behaviorIn the following example, Pubs is the database, server1 is the server that has the source database, and server2 is the server that has the standby database.
On server1, follow these steps:
- Modify the recovery model for the Pubs database to FULL by using the following code:
alter database pubs set recovery full
- Remove the "guest" user from this database by using the following code:
Note If you are using SQL Server 2005, the guest user cannot be dropped. However, the guest user can be disabled by revoking its CONNECT permission and running REVOKE CONNECT FROM GUEST within any database other than the source database or the standby database.
- Add two SQL Server logins by using the following code:
sp_addlogin 'testlogin1', @passwd='pwd1', @defdb='pubs'
sp_addlogin 'testlogin2', @passwd='pwd2', @defdb='pubs'
- Perform a complete backup of the Pubs database by using the following code:
backup database pubs to disk = 'c:\pubs.bak' with init
- Remove the Pubs database from server2.
- Restore the complete backup that you created in step 4 of the previous procedure on server2 in STANDBY mode. To do so, use the following statements:If you try to connect to server2 using the login testlogin1 or testlogin2, the login fails because testlogin1 and testlogin2 do not exist on this server. However, adding these logins to server2 does not permit access to the Pubs database.
drop database pubs
restore database pubs from disk = 'c:\pubs.bak'
with move 'pubs' to 'c:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf',
move 'pubs_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf',
standby = 'c:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.udf'
Steps to resolve the behaviorOn server1, follow this step:
- Run the following query on server1 to get the SID information for the logins that you created in step 3 of the previous procedure:The query returns output that is similar to the following output:
select name, sid from master..syslogins where name in ('testlogin1', 'testlogin2')
(2 row(s) affected)
- Drop the logins testlogin1 and testlogin2 from server2 (if you created them in a previous procedure). To do so, use the following code:
- Run the following queries to create testlogin1 and testlogin2 on server2 by using the following code:
sp_addlogin 'testlogin1', @passwd='pwd1', @sid=SID value
sp_addlogin 'testlogin2', @passwd='pwd2', @sid=SID value
- After the logins are created, connect to server2 by using the login credentials for testlogin1 or testlogin2.
- After you connect to the server, run a SELECT query against the Pubs database.
- syslogins in the master database.
- sysusers in the Pubs database.
Article ID: 303722 - Last Review: Sep 12, 2008 - Revision: 1