March 23, 2009 at 2:19 pm
I had originally submitted this issue under a different Subject line, but I messed up the explanation so badly that I would like to start over.
Here's a typical login user setup:
sp_grantlogin @loginame = 'TEAMWORK\akruesse'
ALTER LOGIN "TEAMWORK\akruesse" with DEFAULT_DATABASE=[TEAMWORKDB]
GO
CREATE USER "Agnes_Kruessel" for login "TEAMWORK\akruesse"
GO
Current_User used to return the actual db user name "Agnes_Kruessel", which is the desired result.
However, since moving to "Windows Authorization" login Current_User now returns "DBO"
I am trying to make sense of the "CREATE USER" statement explanation in "SQL Server 2005 Books on line" There is a "Caution" there that suggests using the catalog views as part of the create, however the usage is not clear to me. I can see that the sys.database_principals view holds relevant info, but how do I make use of it?
Remember the goal is to reurn the database user name.
Thanks in advance,
Joel
Takauma
March 23, 2009 at 3:07 pm
[font="Verdana"]Okay, so you have used Windows authentication.
I did a quick check on a database here which uses Windows authentication. There's a login created for a group of which I am a part, but not a login for me specifically. If I run the following script:
select system_user as [system_user],
current_user as [current_user],
user as ;
... it returns my Windows login name three times.
What do you get?
[/font]
March 23, 2009 at 4:05 pm
Bruce:
With the user example above,
select system_user as [system_user],current_user as [current_user],user as ;
returns:
"TEAMWORK\akruesse" , "DBO", "DBO"
Desired value would be "Agnes_Kruessel"
If you Windows login and database user name are the same it will be hard to tell which attribute value you are actually getting for current_user and user.
Takauma
March 23, 2009 at 4:31 pm
Takauma (3/23/2009)
Bruce:With the user example above, returns:
"TEAMWORK\akruesse" , "DBO", "DBO"
Desired value would be "Agnes_Kruessel"
[font="Tahoma"]
Interesting. Have you tried creating a login for a Windows AD group that has rights to the database? This is usually how I recommend security be set up, rather than individual user names.[/font]
Takauma (3/23/2009)
If you Windows login and database user name are the same it will be hard to tell which attribute value you are actually getting for current_user and user.
[font="Tahoma"]
Perhaps. But why do you need them to be different?
[/font]
March 23, 2009 at 6:00 pm
If you are referring to an database role, yes we do have such a role set up.. and all the db user are added to it. I left that out because membership in the group doesn't have any affect on the behavior I am complaining about.
Or... are you referring to something else when you say "group"?
Joel
Takauma
March 23, 2009 at 6:28 pm
[font="Verdana"]No, database roles are different.
If you are using Windows Authentication, then (hopefully) you have some sort of Windows directory service (Active Directory or Kerberos or something of that nature.)
Create a group there, into which you put the users you want to have access to your database. So for example, you might have a SQL Server Administration group. (A nice feature of these groups is that you can assign then e-mail addresses, so they also double as a way of contacting all of the people within the group.)
Now create a login for that group in SQL Server, and grant that login the rights it needs. That will grant that permission to all of the users within that group. It stops you having to manage users on an individual basis.
I did a bit more checking in our set up, and it turns out there is actually a database user with my network login ID in the database. So that side should work as you want it.
[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply