SQL Server Database Administrators have always come across scenarios where they need to restore a copy of the latest production database to a test or development environment. Some of the challenges that could be faced by the Database Administrators are to Obtain Exclusive Access to Restore a SQL Server Database; otherwise the restore process is going to fail. Secondly, once the database is restored they also need to fix the Orphaned Users within the SQL Server Database. In my previous article titled “Obtain Exclusive Access to Restore a SQL Server Database” we have already discussed how to Obtain Exclusive Access for a SQL Server Database before restoring a database. In this article you will see the steps that a Database Administrators needs to perform in order to fix the Orphaned User issue once they restore a copy of a database backup to another instance of SQL Server.
How Does SQL Server Manage SQL Server Logins
A SQL Server user should have a valid SQL Server Login in order to access the particular instance of SQL Server. A valid SQL Server login is an authentication user who has permissions to get connected to a particular Instance of SQL Server to access the database. In SQL Server 2005 and higher versions the SQL Server Logins are visible in sys.server_principals and sys.syslogin system catalog views. Once a login has permissions to logon to the SQL Server Instance then, the login can access the individual databases on the SQL Server Instance by adding itself as a database user. Each database user will be mapped with a valid SQL Server login for every instance of SQL Server. However there are two exceptions to this, guest account and Windows group memberships. Information related to the mapping of SQL Server Login to a Database User is visible in sys.sysusers system catalog view within the User Database. The sys.sysusers system catalog view will also have detailed information related to the permissions available for the database user along with the name and SID of the corresponding SQL Server Login.
Who is an Orphaned User
An Orphaned User in SQL Server is a database user for which a valid SQL Server Login is not available or it is wrongly defined in the instance of SQL Server, thereby not allowing the user to get connected to the database to perform activities. There are basically two scenarios which results in having Orphaned Users and they are:-
- A SQL Server Login was accidently dropped
- A database is restored with a copy of database from another SQL Server Instance
Example
In this example we require two instances of SQL Server. You will be initially creating a database named OrphanedUsers on SQL Server Instance named SQLServerA along with two SQL Server Logins UserA & UserB. The next step will be to take a Full database backup of OrphanedUsers database on SQLServerA instance and restore the same on SQLServerB instance. Once the restore is complete as generally expected you should be able to get connected to SQLServerB Instance with either of the SQL Server Logins UserA or UserB and should be able to access the database. Let see does it happen as expected or you will end up in issues.
Activities on SQLServerA Instance
The initial step will be to create a user database named OrphanedUsers using the below TSQL script.
/* Create OrphanedUsers Database on SQL Server Instance SQLServerA */
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’OrphanedUsers’)
DROP DATABASE [OrphanedUsers]
GO
USE [master]
GO
CREATE DATABASE [OrphanedUsers] ON PRIMARY
(
NAME = N’OrphanedUsers’,
FILENAME = N’C:DatabaseFilesOrphanedUsers.mdf’ ,
SIZE = 10240KB ,
MAXSIZE = 100MB,
FILEGROWTH = 1MB
)
LOG ON
(
NAME = N’OrphanedUsers_Log’,
FILENAME = N’C:DatabaseFilesOrphanedUsers_Log.ldf’ ,
SIZE = 5120KB ,
MAXSIZE = 50MB ,
FILEGROWTH = 512KB
)
GO
Once the OrphanedUsers database is created on the SQLServerA instance then the next step will be to create two SQL Server Logins UserA and UserB on SQLServerA Instance by executing the below TSQL script.
/* Create SQL Server Login “UserA” on SQLServerA Instance */
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N’UserA’)
DROP LOGIN [UserA]
GO
CREATE LOGIN [UserA] WITH PASSWORD=N’UserA’,
DEFAULT_DATABASE=[OrphanedUsers],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
/* Create SQL Server Login “UserB” on SQLServerA Instance */
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N’UserB’)
DROP LOGIN [UserB]
GO
CREATE LOGIN [UserB] WITH PASSWORD=N’UserB’,
DEFAULT_DATABASE=[OrphanedUsers],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
The next step will be to create two database users namely UserA and UserB who has access to OrphanedUsers database on SQLServerA by executing the below TSQL script.
/* Create Database User “UserA” on OrphanedUsers Database on SQLServerA Instance */
USE OrphanedUsers
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’UserA’)
DROP USER [UserA]
GO
CREATE USER [UserA] FOR LOGIN [UserA]
ALTER USER [UserA] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember N’db_datareader’, N’UserA’
EXEC sp_addrolemember N’db_datawriter’, N’UserA’
GO
/* Create Database User “UserB” on OrphanedUsers Database on SQLServerA Instance */
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’UserB’)
DROP USER [UserB]
GO
CREATE USER [UserB] FOR LOGIN [UserB]
ALTER USER [UserB] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember N’db_owner’, N’UserB’
GO
Verify User and SID value in SQLServerA Instance
Execute the below TSQL Scripts to verify the SID values in system catalogs.
SELECT name, sid, default_database_name FROM master.sys.server_principals
WHERE name IN (‘UserA’,’UserB’)
SELECT name, sid, loginname, dbname FROM master.sys.syslogins
WHERE name IN (‘UserA’,’UserB’)
SELECT name, sid FROM OrphanedUsers.sys.database_principals
WHERE name IN (‘UserA’,’UserB’)
SELECT name, sid FROM OrphanedUsers.sys.sysusers
WHERE name IN (‘UserA’,’UserB’)
The next step will be to take a full database backup of OrphanedUsers database on SQLServerA Instance. Once the database backup has completed successfully then you need to restore the OrphanedUsers database on SQLServerB Instance and hope that UserA or UserB should be able to get connected to OrphanedUsers database on SQLServerB Instance without any issues.
Execute the below TSQL script to take the backup of OrphanedUsers database using the database compression feature in SQL Server 2008. For more information related to the database backup compression feature in SQL Server 2008 you can refer to the article titled “How to configure and Use Database Backup Compression in SQL Server 2008”
BACKUP DATABASE OrphanedUsers
TO DISK= ‘C:DatabaseBackupsOrphanedUsers.BAK’
WITH COMPRESSION
Activities on SQLServerB Instance
Once you are connected to the SQLServerB Instance using the sysadmin permissions then you need to execute the below mentioned TSQL script which will restore the OrphanedUsers database.
RESTORE FILELISTONLY
FROM DISK = N’C:DatabaseBackupsOrphanedUsers.BAK’
GO
RESTORE DATABASE [OrphanedUsers]
FROM DISK = N’C:DatabaseBackupsOrphanedUsers.BAK’
WITH
MOVE N’OrphanedUsers’ TO N’D:DatabaseFilesOrphanedUsers.mdf’,
MOVE N’OrphanedUsers_Log’ TO N’D:DatabaseFilesOrphanedUsers_Log.ldf’,
RECOVERY, NOUNLOAD, REPLACE, STATS = 20
GO