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
You can use sp_change_users_login 'Report' to find out if there are orphaned users. (users with no login)
There are users like 'Guest', 'dbo', 'INFORMATION_SCHEMA' that will not have logins associated because these are defined by the system, but if there are users like 'George', etc. then these are most likely users that were created at one time by users or admins. The above command will help you to know which users are orphaned and can be fixed by creating logins and associating them with the user.
When a sysadmin user logs in, they do not necessarily have a user for every database because they can query any database. Those with specific permissions db_datareader or db_datawriter, etc. will need to have a user in the database that they are trying to query because they are limited to permissions assigned.
HTH.
|||One possible senario is, this database might be restored from another sql server instance and that server may be having these logins-users mapping. Once the database is shifted from one server to another the user-login mapping breaks though the logins are there in the new instance. you need to remap the orphaned user with the logins using sp_change_users_login
Madhu
|||Thanks Ben and Madhu..for the useful piece of info!!!