54

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?

1
  • 1
    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. May 18, 2017 at 0:26

4 Answers 4

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
5
  • 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. Jan 11, 2016 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, 2020 at 13:34
  • To account for AD accounts I would change it to: WHERE [type] IN (N'S',N'U')
    – Asher
    Jun 15, 2020 at 13:55
  • 2
    @Asher You have to enable in the 'check for object existence' in scripting settings. Jun 15, 2020 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, 2022 at 22:01
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;
2
18

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
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
1
  • 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, 2018 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.