here's a very similar version when compared to Jasons;
if a user matches it's login SID, it fine and nothing needs to be changed.
If the SID doesn't match , it builds the ALTER USER command
if the login is missing, it builds a CREATE LOGIN command(with a default password!), and also the ALTER USER command.
depending on your situation, you might not need to create missing logins
WHEN svloginz.name is not null and dbloginz.sid <> svloginz.sid
THEN '--Login Exists but wrong sid: remap!
ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(svloginz.name) + ';'
ELSE 'CREATE LOGIN ' + quotename(dbloginz.name) + ' WITH PASSWORD=N''NotARealPassword'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;
ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(dbloginz.name) + ';'
from sys.database_principals dbloginz
LEFT OUTER JOIN sys.server_principals svloginz
on dbloginz.name = svloginz.name
WHERE dbloginz.type IN ('S','U')
AND dbloginz.name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys')
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!