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

Closing out on 2024

It's the holiday season again, already! This year has been marked by significant advancements in Microsoft Fabric, and Power BI in speci...