Check if a user exists in a SQL Server database

Question

I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database.

This is what I have tested:

USE [MyDatabase]
GO

IF NOT EXISTS (SELECT name 
                FROM [sys].[server_principals]
                WHERE name = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool] 
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO

But, this code SELECT name FROM [sys].[server_principals] doesn't return if that user exists in MyDatabase.

How can I check if an user exists in MyDatabase?

Keep in mind that the sys.database_principals contains roles and users together, so one must not forget to at filter the users. I am updating the final query against the currently marked answer for easy reference. — Moiz Tankiwala, May 18 '17 at 00:26

score 45 · Accepted Answer · edited Jun 25 '19 at 11:45

45

Use sys.database_principals instead of sys.server_principals.

So the final query would look like this (accounting for the user filter):

USE [MyDatabase]
GO

IF NOT EXISTS (SELECT [name]
                FROM [sys].[database_principals]
                WHERE [type] = N'S' AND [name] = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool] 
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO

edited Jun 25 '19 at 11:45

CodeZombie

103
4

answered Jan 11 '16 at 13:58

Chris Aldrich

4,906
5
33
54

3

A quick way to get this (or other object existence checking) is to right click on a database object and select "DROP And CREATE TO" which will generate the proper IF NOT EXISTS clause. – LowlyDBA - John M Jan 11 '16 at 14:36
@LowlyDBA it doesn't work in MSSQL 2017 with SSMS 18.5. There is no 'if not exists' just a pure drop and create. – Asher Jun 15 '20 at 13:34
To account for AD accounts I would change it to: WHERE [type] IN (N'S',N'U') – Asher Jun 15 '20 at 13:55
2

@Asher You have to enable in the 'check for object existence' in scripting settings. – LowlyDBA - John M Jun 15 '20 at 17:11
@Chris Aldrich: Do you know how to do this for multiple users? (e.g., adding 10 users to 10 separate databases)? – monamona Sep 29 '22 at 22:01

score 27 · Answer 2 · answered Jan 11 '16 at 16:15

27

I use SUSER_ID() and USER_ID() for this kind of things:

-- Check SQL Server Login
IF SUSER_ID('SomeLogin') IS NULL
    CREATE LOGIN SomeLogin WITH PASSWORD = 'SomePassword';

-- Check database user
IF USER_ID('SomeUser') IS NULL
    CREATE USER SomeUser FOR LOGIN SomeLogin;

answered Jan 11 '16 at 16:15

spaghettidba

11,266
30
42

6

As per Microsoft's recommendation [https://learn.microsoft.com/en-us/sql/t-sql/functions/user-id-transact-sql], the USER_ID wil be phased out in the near future and the recommended function to use instead is DATABASE_PRINCIPAL_ID [https://learn.microsoft.com/en-us/sql/t-sql/functions/database-principal-id-transact-sql] – Moiz Tankiwala May 18 '17 at 00:24
1

Link is broken. New link: https://learn.microsoft.com/en-us/sql/t-sql/functions/user-id-transact-sql – userM1433372 May 29 '19 at 11:11

score 18 · Answer 3 · answered May 18 '17 at 00:35

Further refinement as this would make a more optimal read-

USE [MyDatabase]
GO

IF DATABASE_PRINCIPAL_ID('IIS APPPOOL\MyWebApi AppPool') IS NULL
BEGIN
    CREATE USER [IIS APPPOOL\MyWebApi AppPool] 
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
END
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO

Sir Swears-a-lot · Answer 4 · 2018-09-17T21:42:37.693

2

If you're using resistered servers you can check many servers at once and return a true/false with:

SELECT @@servername,    
    CASE 
        WHEN EXISTS(SELECT name FROM sys.database_principals WHERE name = 'LoginName') THEN 1 
        ELSE 0 
    END AS YesNo

edited Sep 17 '18 at 21:42

answered Sep 17 '18 at 03:39

Sir Swears-a-lot

3,233
2
28
48

You may want to update your answer to mention database_principals instead of server_principals - check the question - it's about users at the database level. – Hannah Vernon Sep 17 '18 at 14:11

Check if a user exists in a SQL Server database

4 Answers4