Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fix orphaned users created WITHOUT LOGIN. Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 5:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
So I have restored a database to a new server and a check reveals a DB-user being orphaned. However, this user must have been created as CREATE USER WITHOUT LOGIN, for on the original server this user is not connected with a login.
Now, how do I solve the orphaned user? Is there a problem, I wonder, since there was no login in the first place? sp_change_users_login does not work here, for it needs a login to connect the db-user with.

Any help?



Greetz,
Hans Brouwer
Post #1356663
Posted Monday, September 10, 2012 5:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 12,889, Visits: 31,844
FreeHansje (9/10/2012)
So I have restored a database to a new server and a check reveals a DB-user being orphaned. However, this user must have been created as CREATE USER WITHOUT LOGIN, for on the original server this user is not connected with a login.
Now, how do I solve the orphaned user? Is there a problem, I wonder, since there was no login in the first place? sp_change_users_login does not work here, for it needs a login to connect the db-user with.

Any help?


there's nothing to fix;
if you create a user without login, it literally never logs in...so there is nothing to change.
you might create a user like that so that you can run EXECUTE AS under higher permissions in a procedure or trigger, but noone will ever use that USER to actually connect.

If you need an example, let me know, but you should be good to go if you fixed the other users that do have logins.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1356670
Posted Monday, September 10, 2012 7:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Tnx, I figured that much, but it is good to read confirmatiuon.


Greetz,
Hans Brouwer
Post #1356726
Posted Tuesday, September 11, 2012 3:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:18 AM
Points: 559, Visits: 1,159
The user may have been created as a login as normal and given database permissions but then if the server login was deleted the user would be left in the database I believe.

If users were sql logins in the database and valid on the old server you need to create the sql logins on the new server and just default them to master database then run sp_change_users_login to link the server login to the database login
Post #1357222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse