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 |