Avatar of Kyliem

asked on 

Scheme login already exists

MS SQL Server 2005

Hi, I have a general query thats bugging me.  I have some demo databses setup and have the usual 'scheme' user assigned etc which is fine and works great.  

However, I have restored a customer databse (lets call it db1) and it seems the user 'scheme' does not have access to it.  ie, No login exists for scheme against it.  I goto 'logins'', select scheme and then 'databse access tab'.  I tick the customer databse as access allowed and specify the login as user 'scheme'.  However, it argues that user scheme already exists and will not allow me to add it.  

The error I get is "Error 21002" [SQL-DMO] user 'scheme'a ready exists.

So I am stuck in a loop, no databse login scheme exists for my databse db1 but if I try adding it then I get error.  As this is a restored databse do I have a user ID mismatch between their server and mine?

I am just trying to understand the logic and reasoning here so no urgency.  Many thanks

Microsoft SQL Server

Avatar of undefined
Last Comment
Avatar of Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

yes ...

? you have restored a database to a different server

so it sounds like you havn't correctly dealt with the "orphaned" user problem

check books online  sorry don't have access currently...
Avatar of Kyliem


Hi, thanks for the pointer.  The solution regards resolving orphaned users entails assigning the SID from the sysusers table (from restored databse) to an alias user.  However, if I query the SID as specified:  select * from sysusers    then all I get as SID is '<binary>'.  

I am also unsure if 'scheme' is an orphaned user since it is common to all my other databases.  Therefore I cannot drop the user since it is an object owner in multiple other databases.  I understand the logic of the trasact help if the user were unique to that database but it isn't.  

The trabsact logins help regards orphans states:-

When restoring a database backup to another server, you may experience a problem with orphaned users. This scenario displays and resolves the problem:

Alias the login janetl to dbo by executing sp_addlogin.
sp_addlogin 'janetl', 'dbo'

Back up a database. In this example, back up Northwind.
TO DISK = 'c:\mssql\backup\northwnd'

Drop the database that was just backed up.

Drop the login.
sp_droplogin 'janetl'

Restore the backed up database.
FROM DISK = 'c:\mssql\backup\northwnd'

The janetl login cannot access the Northwind database unless the guest login is allowed. Even though the janetl login has been deleted, it still shows up (as an orphaned row) in the sysusers table:

USE Northwind
FROM sysusers
WHERE name = 'janetl'

To resolve orphaned users

Add a temporary login using sp_addlogin. Specify the security identifier (SID) (from sysusers) for the orphaned user.
sp_addlogin @loginame = 'nancyd',
    @sid = 0x32C864A70427D211B4DD00104B9E8A00

Drop the temporary alias that belongs to the aliased SID using sp_dropalias.
sp_dropalias 'nancyd'

Drop the original user (now orphaned) using sp_dropuser.
sp_dropuser 'janetl'

Drop the original login using sp_droplogin.
sp_droplogin 'nancyd'

Avatar of rboyd56

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo