SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Issues tracking members of the sysadmin fixed server role

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:


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.

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


No comments.

Leave a Comment

Please register or log in to leave a comment.