I received an email from another DBA asking how to track a user who is

probably mapping into SQL Server via BUILTIN\Administrators. The

BUILTIN\Administrators group has maintained the default server role

membership, meaning it's a member of the sysadmin fixed server role.

How then do you show what user in the database the login maps in as?

Scripts I posted in a previous entry filter out dbo and guest. Therefore, if you use these scripts, will you see said login? The answer is no.

SQL Server 2000 Books Online tells us:

The dbo is a user that has implied permissions to perform all activities

in the database. Any member of the sysadmin fixed server role who uses a

database is mapped to the special user inside each database called dbo.

I'm having problems finding a parallel page in the SQL Server 2005

documentation, but the behavior is still the same. For instance,

execute the following query logged in as a member of the sysadmin fixed

server role on either SQL Server 2000 or 2005:

SELECT SUSER_SNAME() [Login], USER_NAME() [User]

The Login will show as your login (server principal) but User

will show up as dbo. This is by design. Since dbo has "implicit

permissions to perform all activities," it bypasses any security set on

any of the objects in the database. This includes DENY, which normally

trumps all other permissions. That means any login who has role

membership in the sysadmin fixed server role can access any object in

any database on that server. There is no way of reducing those

privileges short of revoking membership in sysadmin.

On a related note, if you are interested in what the permission paths are for a given login, use the system stored procedure xp_logininfo.

You'll want to specify 'all' as the second parameter (@option), in

order to see all paths. Otherwise, you'll just get the first permission

path. Here's an example of a use of the stored procedure:

EXEC master.dbo.xp_logininfo 'MyDomain\MyUser'

For a Windows login that is a member of the local Administrators group

on the server (or a member of a group which is a member of the local

Administrators group), BUILTIN\Administrators will show up under permission path. If BUILTIN\Administrators is a member of the sysadmin fixed server role, privilege will show as admin.