Resetting SUID After Restoring a Database to a Different Server.

Products

• CA Unicenter NSM

Issue/Introduction

When a new login ID, such as cadb, is added to SQL Server, an entry is placed in the syslogins table of the master database and SQL Server creates a SUID internally. The SUID is a number used internally by SQL Server to uniquely identify a login id. The login id and SUID are placed in the database, such as the CAIRPTDB sysusers table. When a backed-up database is restored to a different server, the SUID's of the new server may not match the restored database. The following example explains how to solve this problem.

The database CAIRPTDB bas backed up on SERVER1 and restored to SERVER2. After restoring CAIRPTDB to SERVER2:

1. Select the MSSQL ENTERPRISE MANAGER icon.
   
   
2. From the menu bar, select Tools SQL Query Tool.
   
   
3. From the query dialog box, select New Query.
   
   Note: All queries are case sensitive.
   
   
4. From the list of databases on SERVER2, select the master database (DB: master) and enter the following query.
   
   select suid,name from syslogins
   Select the Execute Query Button.
   
   Note: Make a note of the SUID for cadb
   
   
5. Select CAIRPTDB from the list of databases and enter the following command:
   
      select suid,name from sysusers Select the Execute Query Button.
   
   Note: Make a note of the SUID for cadb.
   
   If the SUID for cadb from Step 4 is the same as Step 5, then you do not need this procedure. If the SUID is different, then you need to change the SUID for cadb on CAIRPTDB to match the SUID for cadb on the master database.
   
   For example, if the SUID for cadb on CAIRPTDB is 4 and should be 11, follow these steps:
   
   
   1. From the Query dialog select CAIRPTDB from the database list.
      
      
   2. Enter the following SQL queries and select the Execute Query button after the go statement of each query.
      
      
      sp_configure 'allow updates', 1
      go
      reconfigure with override
      go
      update sysusers set suid = 11 where name = cadb
      go
      sp_configure 'allow updates',0
      go
      reconfigure with override
      go.
      
      
   3. Repeat Steps 4 and 5 in the previous procedure. You should see the SUID of CAIRPTDB and the master database are the same.

Environment

Release:
Component: TNGDB