In the past, whenever you used MS SQL mirroring, or log shipping, you'd get this problem when you tried to bring a database back up after a failure event. The problem was that the security principals stored in a database mapped back to security principals stored in the
master database, and they did so by SID. So even if you had security principals with the same name in your
master database on your fail-over node, they wouldn't match up and you wouldn't be able to log in any more.
In SQL Server 2012, Microsoft introduced the idea of contained users, which are exactly what they sound like; users exist wholly within the database in which they're defined. So when you have a fail-over event, your database is back up and accessible straight away.
Unfortunately it created an issue. It's not really SQL Server's fault. We were developing some new applications that used Entity Framework as an ORM layer. We were seeing lots of errors turning up in the SQL Server run time logs similar to:
Now, for our own reasons many of our contained users had the same login name (and even the same password), but we had distinct connection strings for every database and data intent. And it would appear that connections to the SQL server were trying to cross domains and access databases they didn't have access to. Sometimes connections/queries were being attempted against the
master database for no apparent (deliberate, explicit in code) reason.
I'm not sure exactly where to place the blame, that's the developer's call, but it could have been explicit code bugs, or a quirk in EF's connection pooling making assumptions where the server name and the login/user names and passwords that they must be interchangeable (which in the old world they may have been). But it would be really interesting to see which logins were trespassing. For what it's worth I also saw similar behaviour in Service Bus for Windows, despite it having distinct connection strings for Management, Container, and Gateway databases - I don't think they had contained users in mind at any point when making that software, which is odd given its origins in Azure.
Anyway, what's the problem? The problem is that the SID is locked away in the
sys.database_principals table as a blob, and the only place you ever see the familiar string representation (S-1-9-3-...) is in the error log. If it were a Windows user SID (S-1-5-21-...) we could map it straight to a username easy-peasy, but not for SQL SIDs.
There are T-SQL functions for translating SIDs to login names and back again but they either didn't work for me, or expected/returned the binary representation of the SID which was useless.
So I wrote some Powershell to solve my problem. And here it is:
It takes a single argument, which is the initial connection string. If the user you're running the script as has
sa rights on the database, for example, you can invoke it like this:
Any .Net SQL data provider connection string is fair game, but the first thing the script attempts to do is change to the
master database. It's going to Just Work™ if you're using an account with
sa, but I'm sure there are less brutal ways to get rights; I'm not a SQL Server guy.
After that, it's going to retrieve a list of databases from the
sys.databases table, then iterate through them to collect a list of principals of type
WINDOWS_USER. Once we've got them we invoke some
System.Security.Principal.SecurityIdentifier magic on the blob containing the binary representation of the SID (which just comes back as an array of bytes,
byte) to turn it into the string format we know and love. That's this bit:
The script outputs an array of all the security principals found, so you can
Where-Object your heart out to find the one you're looking for and the database it originated in.
For me, I get stuff like (details obfuscated to protect the innocent):
Exciting, isn't it?