I have a user which can login in sql server. Now i need to add user in database, however i am not sure if that user already exists in database or not. So i need script, which checks if the user exists in database or not, and if doesn't it should add that user in that database.
How can i do that?
You can't have a duplicate user, so you could just try and create it and handle the error if it's already there.
Look into the user catalog view, sys.database_principals:
select * from [dbname].sys.database_principals
where name = 'loginname';
To be 100% accurate you need to check by user's SID, not by name:
select * from [dbname].sys.database_principals
where sid = SUSER_SID('loginame');
Note that users may have access already through group and role membership, but that is a separate topic.
I think this worked:
IF DATABASE_PRINCIPAL_ID('login') IS NULL
BEGIN
CREATE USER [username] FOR LOGIN [login]
END
^ this only works if the username in database is same as login.
Do a SELECT COUNT(*), and see whether you get a 0 or a 1.
