0

On my production system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns CPSDOM\mconnors.

We use Windows authentication on our DBs.

On my test system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns dbo.

So I my test system I went to security/logins/ Login Properties/UserMaping and entered in CPSDOM\mconnors for the user. When I run SELECT CURRENT_USER, I still get back dbo. I went back to the Login properties/UserMapping to verify the change was still there and it was.

What am I missing?

1

If you want the same result on both systems, your choices are:

  1. If you always want to know the server-level Windows account in use, change your code to use ORIGINAL_LOGIN() in both places instead of relying on database mapping (or use SUSER_SNAME()).
  2. Add your login to the sysadmin role on the production system.
  3. Create a new login on your test system (or at least be sure you know the sa password), remove this login from the sysadmin role, and make sure they are no longer the database owner (e.g. ALTER AUTHORIZATION ON DATABASE::dbname TO sa;). You may then need to manually add them as a user and add permissions within the database (sorry, this is my least preferred option, and I didn't feel like messing up my own login on my system to test it).
1

It means you have database owner ("db_owner") privileges on the database.

You can see this in "User Mapping" tab in Database Properties.

enter image description here

And here you can see current user and login.

enter image description here

Thanks

0

CURRENT_USER will give you the name of the current security context., which if you are member of the db_owner group is dbo.

If you want your NT login to be displayed try using SYSTEM_USER or SUSER_NAME() instead:

SELECT SYSTEM_USER, SUSER_NAME(), CURRENT_USER
CPSDOM\mconnors| CPSDOM\mconnors| dbo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.