[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error 21002: [SQL-DMO] user already exists

Posted on 2002-05-10
3
Medium Priority
?
19,959 Views
Last Modified: 2011-08-18
hi all

i restored a database called db1

owner of the tables in this database is "owner1" but in my sqlserver "owner1" doesn't
exist.=20 so i create a user "owner1"

when i check the box "db1 can be accessed by owner1", i got this error : = "Error 21002: [SQL-DMO] user owner1 already exists."

what can i do?

Thanks
0
Comment
Question by:denam
3 Comments
 
LVL 1

Accepted Solution

by:
Mikhail Peterburgskiy earned 800 total points
ID: 7003020
I had similar problem when restored db from one server to another.  I fixed it using sp_change_users_login

From MSDN:
sp_change_users_login
Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.

Syntax
sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @LoginName = ] 'login' ]

You can find documentation in MSDN online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp
0
 
LVL 1

Expert Comment

by:ColinSnelling
ID: 7005820
sp_change_users_login only works with SQL Server logins.

This is if you use NT logins :

There is a link between the users in a database (stored in sysusers) and the logins (syslogins) on the sid field.  I'm pretty sure this contains the Windows sid (security id) not a SQL Server one.  Say we have a user ColinS in database db1 and this is linked to NT login MyDomain\ColinS.  If you restore a database from another server and this database was a user ColinS but linked to LocalServer\ColinS then although the users are the same there is no link between sysusers and syslogins.

Enterprise Manager in SQL 7 still showed the user so it was easy to delete.  Enterprise Manager in SQL 2000 doesn't so you have to look at sysusers in Query Analyser and then use sp_revokedbaccess to remove the offending user.
0
 

Expert Comment

by:muktamariwala
ID: 11150093
I am trying to attach a database to a new sql server onwhich the userid already exists.However this useris the ownerof sometables intehdatabase.
when Irun a select query "select * from employee" for eg. it says Invalid object name.
However if i write the query like this::
"select * from username.employee" then it works even though I logged in with username.
When i try to associate this user withdatabase access it gives error"username already exists"
Kindly give the solution
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question