How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q303722
This article describes how to grant access to a database that has been loaded in STANDBY state from another server when the "guest" user is disabled because of security reasons. The information in this article applies only to SQL Server logins, and therefore, to servers that are configured to use "SQL Server and Windows Authentication." This procedure cannot be used for Microsoft Windows NT logins or Windows NT groups.

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 behavior

In 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:
  1. Modify the recovery model for the Pubs database to FULL by using the following code:
    alter database pubs set recovery full
  2. Remove the "guest" user from this database by using the following code:
    use pubs go sp_dropuser 'guest' go

    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.
  3. Add two SQL Server logins by using the following code:
    sp_addlogin 'testlogin1', @passwd='pwd1', @defdb='pubs' go sp_addlogin 'testlogin2', @passwd='pwd2', @defdb='pubs' go
  4. Perform a complete backup of the Pubs database by using the following code:
    backup database pubs to disk = 'c:\pubs.bak' with init
On server2, follow these steps:
  1. Remove the Pubs database from server2.
  2. 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:
    drop database pubs go 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'
    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.

Steps to resolve the behavior

On 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:
    select name, sid from master..syslogins where name in ('testlogin1', 'testlogin2')
    The query returns output that is similar to the following output:
    name                 sid -------------------- --------------------------------------testlogin1           0xED10269A01E2654BA89E33D42AEDFAAF testlogin2           0x704C5B2CB4DB234EAE89BFBCE7B6A46F (2 row(s) affected)
On server2, follow these steps:
  1. Drop the logins testlogin1 and testlogin2 from server2 (if you created them in a previous procedure). To do so, use the following code:
    use master go sp_droplogin 'testlogin1' go sp_droplogin 'testlogin2' go
  2. Run the following queries to create testlogin1 and testlogin2 on server2 by using the following code:
    sp_addlogin 'testlogin1', @passwd='pwd1', @sid=SID value go sp_addlogin 'testlogin2', @passwd='pwd2', @sid=SID value go
  3. After the logins are created, connect to server2 by using the login credentials for testlogin1 or testlogin2.
  4. After you connect to the server, run a SELECT query against the Pubs database.
There are two system tables that are used to store the login information for the Pubs database:
  • syslogins in the master database.
  • sysusers in the Pubs database.
The syslogins table contains all the logins that were created on the server. The sysusers table contains the users that are mapped to the database by using the SID field. If you back up the database on one server and restore the database on a second server, the backup retains the SID value for the users in the sysusers table. However, because the SID values in the syslogins table are different, users cannot query the standby database. You can correct this problem by creating logins with the same SID value as the source server.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
240872 How to resolve permission issues when you move a database between servers that are running SQL Server

Αναγνωριστικό άρθρου: 303722 - Τελευταία αναθεώρηση: 12/08/2005 22:02:53 - Αναθεώρηση: 6.4

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbhowtomaster kbhowto kbinfo KB303722