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

Microsoft Fabric Documentation (for Admins)

Maybe this topic is not the most sexiest of them all, but it certainly has the same (or maybe even more) amount of value for YOU! And.. anot...