Ms Sql Error 21002
Contents |
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads
Mouser
on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | digikey ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2000 Forums SQL Server Administration (2000) error 21002:[SQL-DMO]User 'xxx' already exists Reply to Topic Printer Friendly Author Topic jshurak Starting Member 44 Posts Posted-11/14/2006: 16:53:52 Hello everyone. This is my first post so I hope someone can help me out. After extensively google-ing this error message I have not had any luck resolving this issue. We restored our test server from our production server to test an upgrade. I created a login for a user so they could test, but when I try to give them access to specific databases I get this error: error 21002:[SQL-DMO]User 'xxx' already exists. I have run this procedure with no luck:SP_CHANGE_USERS_LOGIN UPDATE_ONE, 'username','login_name'I have also tried this one:sp_change_users_login AUTO_FIX, 'my_user'I've manually dropped and added the user from the system tables but for some reason I can't get past this. I've also checked various links to Microsoft help pages!! AAAGGGHH!! Any help would be greatly appreciated. Thanks ahead of time! Edited by - jshurak on 11/14/2006 16:56:30 khtan In (Som, Ni, Yak) Singapore 17689 Posts Posted-11/14/2006: 17:04:25 tried sp_revokedbaccess & sp_grantdbaccess ?KH mr_mist Grunnio United Kingdom 1870 Posts Posted-11/15/2006: 05:03:09 sp_change_users_login AUTO_FIX, 'my_user'This should work for a sql login assuming that the login name is the same as the user name. Is it a sql login?-------Moo. :) jshurak Starting Member 44 Posts Posted-11/15/2006: 08:28:11 Thanks for the speedy responses. I haven't tried the sp_revokedbaccess & sp_grantdbaccess, I'll give them a try today. It is a SQL login. I have tried the sp_change_users_login AUTO_FIX, but to no avail. I get 'number of orphaned accounts fixed is 0' message.The error occurs when I try to grant specific permissions to databases for the user. Edited by - jshurak on 11/15/2006 08:32:24 jshurak Starting Member 44 Posts Posted-11/15/2006: 09:20:28 I just tried the revoke and grant statements with no luck. mcrowley Aged Yak Warrior 771 Posts Posted-11/15/2006: 11:23:14 R
MSSQL2000 migration database, the same user name when an error message appears: "Error 21002: [SQL-DMO] User" xxx "already exists" Reason: This is what we usually call "orphaned users", the so-called isolated accounts, is a database account only the user name and login name, such users in the http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74949 sysusers system table in the user library, and in the master database in syslogins but there is no corresponding record. Generation of isolated accounts generally two types: 1. Restore the backup database on another machine; Reinstall the system or SQL Server restore http://www.databasesql.info/article/1249326334/ only the user library Solution: The solution is to use the sp_change_users_login to repair. the sp_change_users_login the usage There are three Repair method: Step 1: exec sp_change_users_login 'Report' Orphaned users listed in the current database Step 2: exec sp_change_users_login 'AUTO_FIX', 'username' Can automatically log in to add a user name corresponding to the same name in syslogins Step 3: exec sp_change_users_login 'UPDATE_ONE', 'username', 'login' User Name Mapping for the specified login name. Recommended ASP.NET GridView use of guide MOVE series redo size of the experiment Stored procedures and functions Website optimization is not necessarily static On the SQL Server 2008 Hints (Tips) Using SQL Server CTE recursive query View oracle database archive SQL commands A large community site system [VB.NET] OleDbDataAdapter Fill © Copyright 2015-2016databasesql.infoContact
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job https://www.experts-exchange.com/questions/20299384/Error-21002-SQL-DMO-user-already-exists.html Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Error 21002: [SQL-DMO] user already http://www.tek-tips.com/viewthread.cfm?qid=863149 exists Want to Advertise Here? Solved Error 21002: [SQL-DMO] user already exists Posted on 2002-05-10 MS SQL Server 1 Verified Solution 3 Comments 19,694 Views Last Modified: 2011-08-18 hi all i restored a database ms sql 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 Question by:denam Facebook Twitter LinkedIn Google LVL 1 Best Solution byMikhail Peterburgskiy I had ms sql error 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™ Go to Solution 3 Comments LVL 1 Overall: Level 1 MS SQL Server 1 Message Accepted Solution by:Mikhail Peterburgskiy2002-05-11 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 Overall: Level 1 MS SQL Server 1 Message Expert Comment by:ColinSnelling2002-05-13 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\Coli
FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft SQL Server: Data Transformation Services Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft SQL Server: Data Transformation Services Forum "Error 21002" creating logins in SQL 2000 thread961-863149 Forum Search FAQs Links MVPs "Error 21002" creating logins in SQL 2000 "Error 21002" creating logins in SQL 2000 semperfi91 (MIS) (OP) 17 Jun 04 16:07 After restoring db from backup device I can not create a user and give them access to that db.The login information in the db is retained but not in the security/logins section. Then when I try to create the account in the security/logins section I receive the "error 21002 user xxx already exists."Looked online and at (Article 819261) but the workaround is the problem.Any assistance would be appreciated.Thanks./scott RE: "Error 21002" creating logins in SQL 2000 nigelrivett (IS/IT--Management) 17 Jun 04 18:25 You can drop the users from the database by using drop user commands.Also see sp_change_users_login which may be able to fix everything for you. ======================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. RE: "Error 21002" creating logins in SQL 2000 JayKusch (MIS) 18 Jun 04 18:11 Try looking at your other DBs in the Users tab. 10-1 you will find that the user you are trying to add is listed in there. Remove it and try to add him back in the Security section ThanksJ. Kusch RE: "Error 21002" creating logins in SQL 2000 semperfi91 (MIS) (OP) 21 Jun 04 11:54 Hi.Thanks for the advice.This user is only accessing this one DB.He owns objects which makes dropping him more difficult than it should be.The sp_change_users_login returns the same errors that the GUI