Tuesday, January 12, 2016

SQL (Orphaned) User Without a Login: HowTo Create a Login For The User

Whenever I restore a database from a customer in my development environment I have the issue of orphaned users: users in the database have no corresponding login on the server/instance.
I found this nice answer on SO that uses the sp_change_users_login stored procedure for this.
You can use sp_change_users_login to create a login for users already in the database.


USE databasename                      -- The database I recently attached
EXEC sp_change_users_login 'Report'   -- Display orphaned users
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'Password'
You get the UserName(s) from the sproc when you run it with @Action='Report'.

No comments:

Post a Comment

Thanks for taking the time to provide feedback!

Cheers,
Nicky

Featured Post

Power BI Gebruikersdagen 2026

Today, it's that time of year again! The  Dutch Power BI Gebruikersdagen  are here :-) Last year's keynote, photo credits by PBIG T...