Check If user exists in SQL 2005

Question

Check If user exists in SQL 2005

Ido Amrani-466637

Old Hand

Points: 395
More actions
August 28, 2007 at 9:20 am

#197529

Hi
I want to create a new sql user through T-SQL but i need to be sure that he doesn't exists.
How i checked if user exists?
Thanks'
Ido

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 1

User or login ?

In case of former you can query sys.sysusers in your database and map it to existing login with sp_grantdbaccess, in case of latter you can query master.sys.syslogins; in either case ...Where name = <your_user_login_name>

Ido Amrani-466637 Old Hand Points: 395 More actions · Answer 2

Hi

That exactly what i need, thanks.

by the way, you know how to grant db_owner to user in specific database by way of TSQL?

Thanks again

Ido

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 3

Mark Shvarts 1

SSCertifiable

Points: 5702

August 28, 2007 at 3:04 pm

#729930

EXEC sp_addrolemember N'db_owner', N'login_name'

Ido Amrani-466637 Old Hand Points: 395 More actions · Answer 4

Ido Amrani-466637

Old Hand

Points: 395

August 28, 2007 at 9:42 pm

#729963

Thanks Again

it is very helpful.

Ido

ishanpur SSC Journeyman Points: 91 More actions · Answer 5

Hello

I shall highly appreciate if you please help me and give me the correct syntax

I would like to check whether a user (test2) is present in a database and then delete.

Use ABC <database name>

if exists (select * from dbo.sysobjects where id = object_id(N'[test2]') and OBJECTPROPERTY(id, N'IsUser') = 1)

drop user test2

GO

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 6

sysobjects view does not contain users. Use this script:

if exists (select * from sys.sysusers where name='test2')

drop user test2

GO

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

You should be checking sys.database_principals to see if the user exists or not.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

double post

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Check If user exists in SQL 2005

Cookies on SQLServerCentral