Link to home
Start Free TrialLog in
Avatar of Kyliem
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

Avatar of Lowfatspread
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
Kyliem

ASKER

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.
BACKUP DATABASE Northwind
TO DISK = 'c:\mssql\backup\northwnd'

Drop the database that was just backed up.
DROP DATABASE Northwind

Drop the login.
sp_droplogin 'janetl'

Restore the backed up database.
RESTORE DATABASE Northwind
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
SELECT *
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'

ASKER CERTIFIED SOLUTION
Avatar of rboyd56
rboyd56

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial