April 26, 2017 at 1:42 pm
Comments posted to this topic are about the item Fix Orphan Users for All databases - Fixed Collation Problem
April 28, 2017 at 11:47 am
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
ThanksSaurabh.D
July 26, 2017 at 10:48 am
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.
July 26, 2017 at 11:35 am
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply