How to check if the USER is already created in the database or not in SQL?

user_408389 0
Viewed 72105

Is there is a way that from it I can know if the user(not the login) is already created in the database? I mean the user not the login, since, I know how to check for the login. I need to check for the user that is created inside a specific DB & a role assigned to it.

This is the code for checking for the login:

SELECT name FROM sys.server_principals WHERE name = 'test_user'

but how about the user? Since I need to create the user and assign a role to it if its not created. Otherwise, I will continue without creating.

Thanks

tsql sql
5 Answers
use SomeDatabase
go

/* built-in system function */
select database_principal_id('UserNameHere')

/* check if exists and drop */
if database_principal_id('UserNameHere') is not null
    drop user 'UserNameHere'
go
user_70488660

You might care for this method as well...

IF DATABASE_PRINCIPAL_ID('domain\lanid') IS NULL
BEGIN
    CREATE USER [domain\lanid] FOR LOGIN [domain\lanid] WITH DEFAULT_SCHEMA=[dbo]
    EXEC sp_addrolemember N'db_ApplicationUserRole', N'domain\lanid'
END
user_3123170
Related