EXEC sp_change_users_login crash with 'WITHOUT LOGIN'

Question

EXEC sp_change_users_login crash with 'WITHOUT LOGIN'

zoltix

SSC Eights!

Points: 813
More actions
February 14, 2011 at 8:29 am

#149897

I created user on database in a bad order.
1. create the user in database with 'CREATE USER TEST_USER WITHOUT LOGIN '
2. I created the login
3. I try to link this user/login
But I received an error message.
And I arrive repeated this error with this code
CREATE USER TEST_USER WITHOUT LOGIN WITH DEFAULT_SCHEMA=TEST_USER
GO
CREATE LOGIN TEST_USER WITH PASSWORD=N'__qsdfqsdfqsdfqsdf_', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC sp_change_users_login 'Update_One', 'TEST_USER', 'TEST_USER';
Go
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'TEST_USER' is absent or invalid.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sp_change_users_login is only there for backward compat with SQL 2000, is deprecated and should not be used any longer. It likely can't handle the newer SQL 2005 stuff.

Try

ALTER USER <username> WITH LOGIN = <LoginName>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

hey neat;

what you've done is create an "orphaned" user;

inside the sys tables, a unique SID is assifned to every login or user; you may have encountered this when restoring a database from another server...the user "bob" on my server is not necessarily the same "bob" on your server...if they are, then you run that proc sp_change_users_login ';

to fix it.

in this case, you'll need to drop the database user and add it back; i believe the NO LOGIN prevents the user from being fixed via the proc i mentioned.

to prove they are differnet to yourself, run these two queries...you'll see the sids are not the same.

select name,sid from master.sys.server_principals where name = 'TEST_USER'

select name,sid from sys.database_principals where name = 'TEST_USER'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!