Add user in database?

Question

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?

Answer 1

Do a SELECT COUNT(*), and see whether you get a 0 or a 1.

Answer 2

You can't have a duplicate user, so you could just try and create it and handle the error if it's already there.

Answer 3

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.

Answer 4

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.