24

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
    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 0:26
23

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
  • 2
    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 Jan 11 '16 at 14:36
15

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;
9

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
0

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
  • 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. – Max Vernon Sep 17 '18 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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