Sql-server – Mapping a Login to a User

authenticationloginssql serversql-server-2008users

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?

Best Answer

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).
Related Question