User or role already exist

Question

Post reply

User or role already exist

SkyBVI

SSCoach

Points: 15128
More actions
September 9, 2010 at 9:02 am

#142684

Hi,
A user has a schema owned and has permissions as DYNGRP in (SSMS->databases--> security-->users) in 3 databases.
When he tries to enter the application which uses sql server logins, the error says:-
The server principal "user" is not able to access the database ".." under the current security context.
When i see in SSMS--> security-->login; that user doesnot have any mapping to the 3 databases.
When i try to map it to the particular database and give role as DYNGRP and PUBLIC; then error occurs :
User, group or role already exists in the database.
What should i do?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Toby White SSCertifiable Points: 6187 More actions · Answer 1

It sounds like the server principal does not exist in master.sys.server_principals

Try creating it:

CREATE LOGIN [SQLLogin] with Password = 'cleartext', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

After that you may need to synch the sids if the database account was created on another server.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

It sounds as though you may have restored these databases from another server to here. If this is the case, and these are SQL logins (not NT logins), use this code in each of the 3 databases for each sql login:

sp_change_users_login 'Auto_fix', <loginname>, NULL

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SkyBVI SSCoach Points: 15128 More actions · Answer 3

yes, i restored the database from 1 server to another , i think its case of orphaned users.

Ya that script helped... 🙂

One more question: There are less users in my server level logins wrt to database level users. Does this happen because I just did an upgrade from sql 2000 to sql 2005? How can I bring all those logins back to server level? This is a dev server. The same logins we have in our prod server too, maybe from there? Is it possible?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 4

sp_help_revlogin from MS will help you script out logins from production if you need them.

Typically you don't have the same logins/passwords on development as it's a security issue. Once you create the logins on the dev server, hopefully with new passwords, you won't have to create them again, though you may need to fix orphans with subsequent restores.

Leo.Miller SSChampion Points: 13051 More actions · Answer 5

Run sp_help_revlogin stored proc on the original server. This will create a script to recreate all logins with their password hashs.

If you don't have it you canget it here:

http://support.microsoft.com/kb/918992

Yo need to create both stored procs, sp_hexadecimal and sp_help_revlogins

Note there are slightly different versions for SQL 2000 and SQL 2005/8.

By the way, it's not good practice to have the same logins on prod as on dev, as ssoner or later somone will connect to the wrong server.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Hunk Wang SSC Enthusiast Points: 161 More actions · Answer 6

use yourdbname

exec sp_change_users_login 'AUTO_FIX','yourloginname'

another way out of this is to delete schema & user from your database, recreate login and map itto your 3 database .

Commit to Never Ending Improvement