Fixing SQL Logins

If you ever restored a SQL Server database you probably ran into an issue that the database had a user defined with the exact same name as a login that already exists on your instance of SQL Server. I'm no SQL guru but my gut tells me it has to do with the fact that the SIDs of both users where different—quite naturally—while the names were the same. Also, the fact that you have SQL users and then also SQL login adds up to confusion immensely.

Next, having realized that something is up, you try to delete either user with little success due to this error:

The selected user cannot be dropped because the user owns objects.

I've run into this problem a bunch of times. Searching through Google newsgroups I found this simple fix:

EXEC sp_change_users_login 'Auto_Fix','[UserName]','[LoginName]'

Documentation on the sp_change_users_login stored procedure states this much:

Use this procedure to link the security account for a user in the current database with a different login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing the user's permissions.

Along the same lines I ran into this error today:

You cannot drop the selected login ID because that login ID «
owns objects in one or more databases.

Now, this one I pulled up when exporting data from one database to another. Of course, I had to resolve the issue I described above first. *Sigh*... It so happened that the imported user was granted permissions to stored procedures. What exactly was it holding on? The mighty Google newsgroups revealed the answer once again:

select name from sysobjects where user_name(uid) like '[UserName]'

Knowing what the user still owns you can purge all that and get rid of the offending user name.

Still, I can't help wondering: why not warn, or better yet prevent, these conflicts in the first place. Why not make these cryptic error messages more meaningful?

Magic tip!
Solved my problem straight away, although I had to fine tune some permissions afterwards to allow the user the desired access rights.

I get this error whenever I run the Procedure

Server: Msg 15290, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Action 'Auto_Fix' is incompatible with the other parameter values ('ServiceCenterAdmin', 'ServiceCenterAdmin').


Any ideas?

Nick -
I got the same problem. Here's what I used:

EXEC sp_change_users_login 'Auto_Fix', 'theusername', NULL, 'theuserpassword'

More info here

Excellent! i didnt know this problem could be solved with a few keystrokes and a click! thanks a lot!

Hi ,

I used the UPDATE_ONE method instead to correct the error.

so for example :
EXEC sp_change_users_login 'UPDATE_ONE','username','loginname'

Thanks for this post , it saved my butt !
Lex Penrose

Hey guys. I've tried using the following commands:

EXEC sp_change_users_login 'Auto_Fix', 'theusername', NULL, 'theuserpassword'

and

EXEC sp_change_users_login 'UPDATE_ONE','username','loginname'

and I still get the same error. Any ideas?

The username that's having the problem is TRACKIT65 and I want it to use the login name Apps.

Thanks.

Thanks Guys!

You've just saved me from a long night in in the office!!

Special thanks to everyone who posted here. You all have given me the solutions.

At first I tried
EXEC sp_change_users_login 'Auto_Fix','[UserName]','[LoginName]'
EXEC sp_change_users_login 'Auto_Fix', 'theusername', NULL, 'theuserpassword'
EXEC sp_change_users_login 'UPDATE_ONE','username','loginname'
but all failed.

After looking through everything, I found out that I didn't specify the problematic database (in SQL Query Analyser). Right after I have chosen the correct database, I run this code successfully.
EXEC sp_change_users_login 'Auto_Fix', 'theusername', NULL, 'theuserpassword'

Thanks again.

if this user is dbowner (dbo), you can use this command:

EXEC sp_changedbowner 'userlogin, 'base name'

I also having same problem as above,
I try to use

EXEC sp_change_users_login 'Auto_Fix', [loginid], NULL, [userid]

but come out another error saying
'Procedure or function sp_change_users_login has too many arguments specified.'

Statement below saved me,
EXEC sp_change_users_login 'UPDATE_ONE', [loginid], [userid]

Thanks.

Thanks to everyone who posted here. This is the first place I looked and it took me down the right path. I have never ran across this before so I was at a loss.

If you have a lot of permissions to change, then follow the post in this link. This is what I used and it worked great
http://www.sqlteam.com/item.asp?ItemID=1283

This post really helps me.
I tried on
EXEC sp_change_users_login 'UPDATE_ONE', [loginid], [userid]
and it works!
TQ.

Statement Really helped me

I am trying to synch logins for a SQL-2005 database.

What command do I have to execute to synch logins?

I used the following script which someone already mentioned. Make sure to run Query Analyzer on the database that you have just restored and not master db. Thanks to those who submitted to the forum. I searched the error on google and came upon this forum. The internet is great!! This saved me time and money. It's great to be connected.

EXEC sp_change_users_login 'Auto_Fix', 'theusername', NULL, 'theuserpassword'