Login -User Association. - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Author

Topic

manikandan
Starting Member

35 Posts

Posted - 2002-07-30 : 05:17:44

Hi Guyz,

i've actually created a NT group login. And i created a user for the same using sp_adduser. The user is getting added properly but when i see in EM, i am not seeing any associated logins for the user.

I even tried creating the user through EM interface. Still the user is getting created and i am not seeing the association in the enterprise manager.

And also i tried using sp_grantdbaccess. Still of no use.

Kindly help me out.

cheers
manikandan

Cheers
Manikandan

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-30 : 07:26:32

In Enterprise Manager, navigate to the database, under the "Users" node, double click on the user name. This opens up a "User Properties" dialog box and shows you the associated login name.

--
HTH,
Vyas
http://vyaskn.tripod.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-30 : 07:34:19

I'm seeing the same problem mani is. I have a user named "Administrator" in a user database with a login of <none>. In fact, all of the user databases have the same setting. However, in model, msdb, Northwind and such, the Administrator user is mapped to my NT login.

It seems that if dbo and another user account share the same login, the non-dbo user will show a blank login. I can't verify this though because for some reason I can't create a new user using the same login (the login doesn't appear in the EM list)

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-30 : 08:50:36

This is what EM runs to show users


select DISTINCT o.name, loginname = (case when (o.sid = 0x00) then NULL else l.loginname end), 
user_name(o.gid), o.uid, o.hasdbaccess 
from dbo.sysusers o left join master.dbo.syslogins l 
on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 
and o.status != 0) or (o.sid = 0x00) and o.hasdbaccess = 1)and o.isaliased != 1 
order by o.name

If the sid in sysusers does not match one in master.dbo.syslogins then loginname returns NULL.
This can happen when you are a member of an NT group that is has a server login but no explicit login for your username. If you create a database then your sid will be in sysusers against the dbo user but with no match in syslogins and hence will show up null. However if you look at the properties of the database you will see the actuall username as the database owner.If you modify the query to be


select DISTINCT o.name, loginname = (case when (o.sid = 0x00) 
then NULL else ISNULL(l.loginname,SUSER_SNAME(o.sid)) end), 
user_name(o.gid), o.uid, o.hasdbaccess
from dbo.sysusers o left join master.dbo.syslogins l 
on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 
and o.status != 0) or (o.sid = 0x00) and o.hasdbaccess = 1)and o.isaliased != 1 
order by o.name

then you should see the correct name

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources