Is there a sys-table for user names? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Is there a sys-table for user names?

Author

Topic

Javadeveloper
Starting Member

6 Posts

Posted - 2010-10-31 : 03:11:30

I do this to create a login:


USE master
IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'devlogin')
  CREATE LOGIN devlogin WITH PASSWORD = 'abcd1234', DEFAULT_DATABASE = dev
GO

Then I want to do something like this to create a database user for the dev database:


USE dev
IF NOT EXISTS (SELECT * FROM sys.??? WHERE name = 'devuser')
  CREATE USER devuser FOR LOGIN devlogin WITH DEFAULT_SCHEMA = devtables
GO

But is there a sys-table I could query to check if the user name already is defined before I try to create it?

I have browsed a lot for a sys-table to look in but have not yet found one that has this information but maybe someone her knows!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-31 : 05:00:01

Is this you are looking for ?

select * from master.sys.sysusers

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-31 : 05:14:29

sysusers is deprecated, included only for backward compatibility with SQL 2000, should not be used in new development and will be removed in a future version of SQL Server. Please do not recommend the use of deprecated views to people.

In SQL 2005 and later you should rather be using sys.database_principals in the DB in question. So your query would read

USE Dev
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'devuser')
CREATE USER devuser FOR LOGIN devlogin WITH DEFAULT_SCHEMA = devtables

--
Gail Shaw
SQL Server MVP

Javadeveloper
Starting Member

6 Posts

Posted - 2010-10-31 : 06:28:04

Thanks for your replies. I really appreciate your help, but it still doesn't work. I think I already have been into that table before but to be sure did I execute these SQL snippets again now ...

First I created a login like this:


USE master
IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'devlogin')
  CREATE LOGIN devlogin WITH PASSWORD = 'abcd1234', DEFAULT_DATABASE = dev

Then I created a schema like this

CREATE SCHEMA dev AUTHORIZATION dbo

And finally I created a user like this:


USE dev
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'devuser')
  CREATE USER devuser FOR LOGIN devlogin WITH DEFAULT_SCHEMA = dev

I verified that devuser was added to the dev database and then should this select give one record as a result, shouldn't it?

SELECT 1 FROM sys.database_principals WHERE name = 'devuser'

But it didn't. It seems that the information (name) about the user I have created is stored in some other table. I think it is strange if there isn't some sys-table for users also like this user as there is the sys-table for logins.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-31 : 11:45:49

Why are you creating the schema in master? You're creating it before you change database.

I ran your code (with just the DB name changed and the schema creation fixed) and got exactly the expected results, one row returned from that final query.

USE master
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'devlogin')
  CREATE LOGIN devlogin WITH PASSWORD = 'abcd1234!', DEFAULT_DATABASE = Testing
GO

USE Testing
GO

CREATE SCHEMA dev AUTHORIZATION dbo
GO 


IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'devuser')
  CREATE USER devuser FOR LOGIN devlogin WITH DEFAULT_SCHEMA = dev
GO

SELECT 1 FROM sys.database_principals WHERE name = 'devuser' -- returns the expected one row.

The system table sys.database_principals IS the system table for database users and database roles and it's the user equivalent of sys.server_principals which contains logins and server roles

--
Gail Shaw
SQL Server MVP

Javadeveloper
Starting Member

6 Posts

Posted - 2010-10-31 : 12:12:32

Oups! That is a cut & paste-error

But I believe this is something you know about so I must have made a mistake when I tried it myself and I will try it again soon until I get it to work. I am just doing too many things at the same time right now.

Should I use sys.server_principals instead of sys.sql_logins when querying for the existence of a login? Maybe sys.sql_logins is something deprecated only left for backward compatibility as is the case for sys.sysusers?

A lot of thanks for your help! It is appreciated!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-31 : 12:32:11

sys.sql_logins is not deprecated (as a quick look in Books Online would show). It's a DMV that contains ONLY sql logins (ones with username and password). As such, if you're creating logins that use windows authentication they will not reflect in that view (correctly)

I prefer to use sys.server_principals to check for a login's existence, as both sql and windows logins appear in there. If you're checking for things that only apply to SQL logins (like password, policy, expiration date), then query those from sys.sql_logins.

--
Gail Shaw
SQL Server MVP

Javadeveloper
Starting Member

6 Posts

Posted - 2010-10-31 : 13:52:19

Thanks for that clarification. Then is sys.sql_logins still the table I should query as I mostly use logins from Java EE based components that query the database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources