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
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
Last Comment
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 = 0x32C864A70427D211B4DD0010
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'
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 = 0x32C864A70427D211B4DD0010
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
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.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
? 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...