SQL Server – The dbo user

Background

There is a database that we restore nightly from Production.

The user has been doing a bit of acceptance testing and they called us this morning to let us know that they have too much access.

What a thing, user complaining of too much access?

 

TroubleShooting

Session

Run As User, Check User

CURRENT_USER

SQL

set nocount on
go

set XACT_ABORT on
go

use [pin]
go

DECLARE @login NVARCHAR(256)
DECLARE @user  NVARCHAR(256);

select 
			[STAGE]
				 = 'Before Context Switch'
		  , [SYTEM_USER_BEFORE_SWITCH] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB] 
				= CURRENT_USER
		  , [isSrvRoleMember] 
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		

	
EXECUTE AS LOGIN = 'LAB\AnnMargaret'

select 
		    [STAGE] 
				= 'Within Context Switch'
		  , [SYTEM_USER] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB]
				= CURRENT_USER
		  , [isSrvRoleMember]
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	


/*

	select top 10 *
	from   dbo.pintbl

*/

--setuser
REVERT

select 
		    [STAGE] = 'Post Context Switch'
		  , [SYTEM_USER] = SYSTEM_USER
		  , [CURRENT_USER_IN_DB] = CURRENT_USER
		  , [isSrvRoleMember] = IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		


Image

Explanation
  1. We have 3 sections above
    • Section 1
      • Before Any User Context Change
        • I,the DBA, have all access
    • Section 2
      • Upon User Context Change
        • We use ‘SetUser’ or ‘Execute as Login’ to change the User
        • Queried to ensure user context change
          • SYSTEM_USER
            • The changed User Login account
          • CURRENT_USER
            • If user has db_owner privileges, then dbo returned
            • Else the user’s name in the contextual db
    • Section 3
      • Once change is reverted
        • We are back to ourselve

 

Database

DBO ROLE

sys.database_role_members

SQL

select 
		  [account]
			= tblSDP.[name]

		, [accountType] 
			= tblSDP.[type_desc]

		, [role] 
			= tblSDPR.[name]

from   sys.database_principals tblSDP

inner join sys.database_role_members tblSDRM

		on tblSDP.principal_id = tblSDRM.member_principal_id

inner join sys.database_principals tblSDPR

		on tblSDRM.role_principal_id = tblSDPR.principal_id

where tblSDPR.[name] = 'db_owner'

-- skip user dbo
and   tblSDP.[name] != 'dbo' 

;


Output

 

Explanation
  1. Only one account, dataVal, has db_owner privileges
    • dataVal
      • dataVal is a SQL Account
      • We are looking to track down a Windows Login

 

“DB OWNER”

 sys.databases

SQL


use [pin]
go

select 
		 [database]
			= tblSD.[name]
		, [ownerSID]
			= tblSD.[owner_sid]
		, [owner]
			= suser_sname(tblSD.[owner_sid])

from   sys.databases tblSD

where  tblSD.[name] = db_name()


Image

Explanation
  1. Database’s owner is sa

 

SQL Instance

Server Role Member

sp_helpsrvrolemember

SQL

declare @srvrolename  sysname

set @srvrolename = 'sysadmin'

EXEC [master].[dbo].sp_helpsrvrolemember
		  @srvrolename = @srvrolename

 

Image

Explanation
  1. Our Login is not listed as having sysadmin privileges

 

Account Membership

xp_logininfo

SQL

declare @acctname sysname
declare @option   sysname

set @acctname = 'BUILTIN\Administrators'
set @option = 'members';

EXEC [master].[dbo].xp_logininfo 
		  @acctname = @acctname
		, @option = @option 
		;

Output

Explanation

We found the account is part of the Local Administrator group.

 

Summary

Here is how the account we are tracking ended up with more information than we thought it would have:

  1. OS
    • Active Directory Account
      • Part of Machine’s Local Administrator group
  2. SQL Instance
    • SysAdmin
      • BUILTIN\Administrators has sysadmin privileges
  3. SQL Database
    • dbo
      • All logins that sysadmin have dbo access in each database