SDU Tools: Create SQL Server Login with SID from a database
In SQL Server, both logins (access to the server) and users (access to a database) have a name and a security ID (SID). This leads to problem situations where names might match but SIDs don’t match.
Common Problem
I’ve lost count of the number of times I’ve seen a user restore a database from another server, and then realize that the SQL Server login they need wasn’t present. Then, they create a new login and end up in a lousy situation, because the new login’s SID doesn’t match the SID of the user in the database. I call this a mismatched SID issue.
The error message will tell you that the user doesn’t exist in the database, but when you try to add the user, it will tell you it already exists. That’s the sort of thing that makes people want to throw their mouse through their screen.
Microsoft Solution and why it’s not enough
There is a Microsoft solution for fixing this problem. For a long time, the command that people used was:
sp_change_users_login
Lot’s of people still use that command. If you’re one of the cooler kids, you’ll know that the current way to fix a user is to instead do this:
ALTER USER Blah WITH LOGIN Blah;
It’s described here.
What these commands do though, is change the database SID to match the one for the login. I think they’re fixing the wrong problem.
The problem with this solution though, is that most of these restores aren’t one-offs. And when the next restore is done, you’ll be back in the same problem.
Avoiding the Issue
Now there is of course a way to have avoided this issue in the first place. Instead of just creating a new login and getting a new SID, you could have specified the SID when creating the login. Then it would match and there wouldn’t be an issue.
You could have retrieved the SID by scripting it out when you scripted the login (if you scripted it - most people with this problem haven’t).
Another solution
Another solution would be to retrieve the SID from the database that you can’t access and use it for creating the new SQL login.
In our free SDU Tools for developers and DBAs, we have a tool called CreateSQLLoginWithSIDFromDB that does just that. It’s a long name but it does precisely what it says.
It is quite configurable and has a few parameters (many of them optional):
@SQLLoginName sysname -> Name of the login to create @Password nvarchar(128) -> Password to assign @SIDDatabaseName sysname -> Database to retrieve the SID from @DefaultDatabase sysname -> (Optional) default database for the login @DefaultLanguage sysname -> (Optional) default language for the login @IsCheckExpiration bit -> (Optional default 1) is expiration to be checked? @IsCheckPolicy bit -> (Optional default 1) is policy checked? @SIDDatabaseUserName sysname -> (Optional default @SQLLoginName)
Find out more
You can see an example of calling it in the main image above, and see it in action in the video here:
Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:
http://sdutools.sqldownunder.com
2019-08-21