SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fix Orphan Users for All databases - Fixed Collation Problem


Fix Orphan Users for All databases - Fixed Collation Problem

Author
Message
shiva_info21
shiva_info21
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 289
Comments posted to this topic are about the item Fix Orphan Users for All databases - Fixed Collation Problem
Saurabh Deshpande
Saurabh Deshpande
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 136
Nice and Easy Script.
But,
The sp_change_users_login is deprecated procedure. sp_change_users_login cannot be used with SQL Database.
Below is article to Troubleshoot Orphaned Users (SQL Server)
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server

Thanks
Saurabh.D
Bill Talada
Bill Talada
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4644 Visits: 2224
Hmm, seems like a serious security hole to me, granting database access to a login just because a username matches a login name. I would only manually fix up a user/login if I knew it was the same person. A much better solution would be to create logins across instances using matching SIDs so a fix up never needs done. Windows logins, of course, never need fixed up.
bmg002
bmg002
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9415 Visits: 1892
Where I work, we sometimes have an issue with orphaned users on our test systems. We restore live user databases to test from backup when we need to troubleshoot some obscure issue and don't want to mess up live data.
Since we don't restore the system databases, this can result in orphaned users.
I did a similar script that matches a login and user if the names match and then automatically maps them. If there is no match, it generates a drop script for you to manually run.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search