Creating a SQL user with consistent SID in all environment

October 13, 2017 No comment

When you add a SQL user with the same user ID’s manually on several environments you will see that after a restore this user has no permissions when he tries to acces that database although he seems to be present when you check the SQL properties. The explication is simple, the SID for that user is different, so the SQL Login and the database user are not the same.

SELECT 'Login' AS principal_type,SP.name,SP.sid
  FROM sys.server_principals AS SP
 WHERE name = 'readsoft'
UNION ALL
SELECT 'User' AS principal_type,DP.name,DP.sid
  FROM sys.database_principals AS DP
WHERE name = 'readsoft';

The solution is simple, you create the Login on 1 SQL server and for all other environments you create that login via a script. This way you ensure that the login and user are related.

CREATE Login readsoft WITH password = '******', SID = 0xF3CA088009A6A647A4C1BF84D2202EB8