SQL Server – How to Solve SIDs Mismatching or Orphaned Users

sql serversql-server-2008-r2

I moved and restored a database from a backup into another SQL Server 2008R2 about a year ago. I wasn't aware of the concept of orphaned users. From what I understood, is that we can end up having mistmatched SIDs? So I run the following below query and I see that sid names are not the same for the server and the database for the same login user. So far, users are not complaining but how would I fix this? is there any stored procedure that list them for me? or what problems that I could be facing if I leave everything like it is.

select d.name, d.sid, s.name, s.sid
from sys.database_principals d 
full join sys.server_principals s
on d.principal_id = s.principal_id

Any advice is much appreciated.

Best Answer

There are a couple of possibilities.

First if the orphaned user is a windows login/group (type U or G) then no problem.

-- If the login doesn't currently exist on the server
CREATE LOGIN [Windows Account] FROM WINDOWS;
-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If it's a "SQL Login" (type S) and the login exists then again no problem.

-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If the login does not exist for a "SQL Login" then you have to create the login and that will require knowing the password.

-- Create the login
CREATE LOGIN [login name] WITH PASSWORD = '<strong password>';

Don't forget to make sure your password meets your windows policy requirements. If you still have the old server you can copy the login pretty easily from the other server including the password by getting the HASH of the password from the other server. (There may be some issues depending on if the two servers are different versions).

I did a post on this here: http://sqlstudies.com/2013/03/25/how-do-i-move-a-sql-login-from-one-server-to-another-without-the-password/

Related Solutions

Sql-server – How to obtain SIDs of Sql Server installed NT Service Accounts

From the WMI documentation, I couldn't find any way to directly obtain a service SID, probably because as far as I've seen, service SIDs aren't physical directory objects, and thus couldn't be queried except where they appear as part of ACLs. This would also explain why the values aren't persisted in the registry.

Having said that, the service SIDs definitely do not appear in Win32_SystemAccount. Using PowerShell on a sample machine, you can verify this by running Get-WmiObject -Query "SELECT * FROM Win32_SystemAccount".

Luckily, however, there are alternatives:

Per this TechNet blog post, the non-well-known portion of a service SID is generated by taking a SHA1 hash of the uppercase service name, and then splitting apart the results into five unsigned 4-byte numbers. The following code performs this computation and returns the SID as displayed in Windows:

DECLARE @serviceName nvarchar(128) = 'MSSQL$SQL2008R2DEV';

SELECT
    'S-1-5-80' +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 17, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 13, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 9, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 5, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 1, 4), 2)))
    FROM
    (
        SELECT
            CONVERT(varbinary(20), REVERSE(HASHBYTES('SHA1', UPPER(@serviceName)))) AS BinSid
    ) a;

The binary SID can also be obtained by using the SUSER_SID function (with some extra processing to handle the fixed portion) in lieu of HASHBYTES. (Note that the raw binary SID must have its endianness reversed before conversion and output. Also, the @serviceName variable being nvarchar is important.)

If you have .NET available, doing the hash calculation is trivial. Here's the C# code to do it:

using System.Security.Cryptography;

...

string serviceName = "MSSQL$SQL2008R2DEV";

HashAlgorithm ha = HashAlgorithm.Create("SHA1");
byte[] hash = ha.ComputeHash(serviceName.ToUpper().SelectMany(c => BitConverter.GetBytes(c)).ToArray());

string sid = "S-1-5-80";

for (int i = 0; i < 5; i++)
    sid += "-" + BitConverter.ToUInt32(hash, i * 4);

I suspect the SIDs could be found using classes in the .NET System.DirectoryServices namespace, either by inspecting group membership (fragile), or by some other method. Unfortunately, the methods I tested to directly translate a principal name into a SID failed, so I'm not sure how SQL Server does it (PInvoke?). In any event, computing the hash isn't a big deal if .NET is available (in fairness, the code will be longer without the use of LINQ).
Capture the relevant part of the output of the sc utility as used below, or run this manually if you don't care about being able to automate the solution:
```
sc showsid MSSQL$SQL2008R2DEV
```

Sql-server – What would cause an orphaned ##MS_PolicyEventProcessingLogin##

We upgraded two servers (from SQL 2000) to SQL 2008R2 using in-place upgrade. We started getting these messages in SQL logs after upgrade. We did not change this or any other logins or users during the upgrade process.

My guess is upgrade process left two accounts (##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin##) orphaned.

EXEC sp_change_users_login 'Auto_Fix', '<User Name>' fixed this issue.

Best Answer

Related Solutions

Sql-server – How to obtain SIDs of Sql Server installed NT Service Accounts

Sql-server – What would cause an orphaned ##MS_PolicyEventProcessingLogin##

Related Question