March 6, 2009 at 11:40 am
We had initially set up a client db with "SQL Server Authentication" in place. When it came to schema, many table column defaults such as "EnteredBy" or "ApprovedBy" were defined as "CURRENT_USER". A number of procedures also use "CURRENT_USER" as a parameter or default setting for internal vars. In all situations "CURRENT_USER" faithfully returned the proper value.
Along the way the decision was made to move to "Windows Authentication". Now "CURRENT_USER" returns "dbo" in all cases.
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
EXEC sp_addrolemember N'Planners', N'Agnes_Kruessel'
GO
The "Planners" role is set up as follows:
create role [Planners] AUTHORIZATION [dbo]
The "System_USER()" functions returns "TEAMWORK\akruesse" which is close to the mark; but we need the simple user name "akruesse" returned. My preference is to get "CURRENT_USER" to work as it did before, so that I will not need to update dozens of SPs and ALTER even more table schema.
Takauma
March 6, 2009 at 11:43 am
Current_User will work if not all the users are part of the sysadmin group. That group returns as dbo. That's by design (since it's what sa/dbo really means).
You could chop the domain name off of stuff easily enough with string functions.
First, though, is test a few user names that aren't part of the sysadmin group.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 12:21 pm
By "sysadmin group" are you referring to the user's membership in the "Planners" role, GSquared... and Planners' ownership by dbo?
If so, is there anyway to alter the Planners role to get the behavior we need?
The idea is to keep using "CURRENT_USER" if at all possible. I realize "SYSTEM_USER" combined with a sting function will do the job... I just want to avoid implementing it.
Thanks in advance.
Takauma
Takauma
March 6, 2009 at 1:31 pm
I'm not sure what your Planners group has as rights and such. If it's part of the system administrators group, all logins in it will show as "dbo".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply