CODEWITHSUNDEEP
sqlt-sql
Q8Y
Q8Y

Reputation: 4001

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

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

Upvotes: 44

Views: 85824

Answers (5)

Prisoner ZERO
Prisoner ZERO

Reputation: 14176

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

Upvotes: 3

YearsLater
YearsLater

Reputation: 91

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

Upvotes: 8

marc_s
marc_s

Reputation: 755043

How about:

USE (your database you want to check the user's existence in)

SELECT * 
FROM sys.database_principals
WHERE name = '(your user name to check here)'

sys.server_principals shows you the logins defined on the server level - sys.database_principals shows you the principals (e.g. user accounts) on a database level.

Upvotes: 78

gbn
gbn

Reputation: 432451

This will tell you the matching user name for a given login name

USE MyDB
SELECT
    sp.name AS ServerLoginName,
    dp.name AS DBUserName
FROM
    sys.server_principals sp
    LEFT JOIN
    sys.database_principals dp ON sp.sid = dp.sid
WHERE
    sp.name = 'MyLogin'

Upvotes: 16

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

If you've found sys.server_principals, I'm surprised you haven't found sys.database_principals. You can join the views based on the sid column.

Upvotes: 3

Related Questions