August 8, 2004 at 12:26 pm
I am experiencing a very frustrating propblem. When I run the command "SELECT CURRENT_USER' (in all of my databases), it always returns 'dbo' even though I have specifically changed the database owner to a different user: (sp_changedbowner @login = 'MYDOMAIN\otheruser').
When I login as MYDOMAIN\me, CURRENT_USER returns 'dbo' when using any database, regardless of who the owner is.
Strangely, when I log in as otheruser, the function seems to work properly, returning 'dbo' when using databases that otheruser is the owner of, and returning 'otheruser' when using databases not owned by otheruser.
Does anyone know what the problem could be?
As a side note, I am running the SQL Server 2000 developrs edition (SP3 installed) locally on my machine.
August 8, 2004 at 12:33 pm
Is your login a member of the sysadmin fixed server role? If so, unless explicitly set otherwise, you automatically map into the database as dbo.
K. Brian Kelley
@kbriankelley
August 8, 2004 at 4:07 pm
I have explicity set the username for my login (after changing the db owner):
USE MYDB
sp_adduser @loginame = 'MYDOMAIN\me', @name_in_db = 'me'
Is there another way to explicitly map my login to a username that is not 'dbo'?
Also, even after I removed my login from the 'sysadmin' role in the enterprise manager, I was still getting 'dbo' returned from the CURRENT_USER function.
August 8, 2004 at 9:37 pm
I just tested and even if you are explicitly mapped, if you have sysadmin access in anyway, you still map in as dbo. Check to see if you have sysadmin access through BUILTIN\Administrators or another group.
K. Brian Kelley
@kbriankelley
August 12, 2004 at 2:34 pm
I logged in as another sysadmin user and removed the sysadmin role from my (original) login. Now when I try to connect to SQL server (using my original login), I get the error message: "A connection could not be established...SELECT permission denied on object susers...". For my original login, I am a member of all server roles except sysadmin, with access / dbowner role to all of the databases. Any ideas as to what is going on???
I am trying to develop a database application for a company that I am an emloyee of. Our usernames are contined in the Employees table, and there is a good amount of functionality around accessing info for the current user/employee. It is impossible for me to test as the sysadmin, because none of the security functions work properly (CURRENT_USER(), IS_MEMBER(), etc.) as they all return meaningless dbo info. Does anyone have any suggestions?
August 12, 2004 at 2:56 pm
OK, I got it...in my haste, I had selected the deny roles in the master DB...problem solved
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply