http://www.sqlservercentral.com/blogs/brian_kelley/2006/04/25/issues-tracking-members-of-the-sysadmin-fixed-server-role/ Printed 2014/11/26 04:19PM
Issues tracking members of the sysadmin fixed server role
2006/04/25I 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.