Database user and Corresponding login

L Arvind

Hi everyone, I have a simple question regarding the database users on sqlserver 2005. When i run a 'sp_helpuser' stored procedure in a database to know the usernames and their corresponding loginsName, i find there are some usernames with corresponding loginName as null.I know for sure that for 'Guest' username the corresponding loginName will be Null, but i find many normal database users with Null login names. Can anyone throw some light on this scenario .and what about 'dbo' user in a database can dbo user have a 'NULL' login name or 'sa' login by default . Please help me Thanks in advance. Regards Arvind L

richbrownesq

First thing to bear in mind is the comment in Books Online:

sp_helpuser does not return information about securables that are introduced in SQL Server 2005. Use sys.database_principals instead.

However, there are reasons that a user may have NULL for a login.

1. These users could well be orphaned users. For example, if you restore the database to a new server then the logins may not exist and so the login name will be null. Or, even if the login does exist, it will probably have a different SID so there is no mapping between the user and login (this can be corrected using sp_change_users_login).

2. In SQL2005 it is also possible to create users without logins. The following is a good blog post on it.

http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

HTH!

ggciubuc

dbo is used by sa login to access a database.You find Null for loginName because that user have not an login in server.

Test a scenario with M Studio: create a login 'aa' and in a database create user 'bb' that correspond that login then run sp_helpuser before and after you delete login aa.

If a login has a database default that login has an user database that correspond for it.

Default sa login has master database as default database so, if dbo has a null value in that column from sp_helpuser then dbo has't a login for that database

L Arvind

Thanks guys its indeed a good piece of info!!!

Laurentiu Cristofor

You may also want to have a look at these posts I recently wrote on this topic:

http://blogs.msdn.com/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx

http://blogs.msdn.com/lcris/archive/2007/09/12/basic-sql-server-security-concepts-permissions-and-special-principals-sa-dbo-guest.aspx

To add to Gigi's message, dbo is not used only by sa, but by any sysadmin member as well.

Thanks

Laurentiu