I had authored a post on this a couple of weeks back to detect mismatch between DBO User & database owner. I had also included how to find the various groups a windows login belongs to using the login’s token in sys.login_token. I continued working on this script and finished it last week. One of the problem’s was the login token view is only useful when you have logged in via the User itself. If you are an admin you will get results from the Admin login token, which isn’t useful in detecting permissions.

So, this new script I wrote will give you the following information, which is very very useful when doing security audits, security permission validation and especially useful if you manage a lot of databases and your SQL Server has a lot of AD Group logins etc. Here is what my script does:-

1. DBO Owner Login to Database Owner Mapping.
2. Logins which are part of AD Groups and has DBO rights in each database
3. Members of DB_OWNER role in each database excluding the DBO user itself (including permissions via AD Groups).
4. Summary: Identify those users who are DBO mapped logins, but they do NOT have explicit login NOR are they are part of DB_OWNER role.

You can think of this as a complete security auditing script for AD Logins, groups, Database Owner/DBO validity.

T-SQL SCRIPT for Security Auditing of DBO Access & AD Groups

set nocount on
go
Create table #TmpTableSec1 (database_name varchar(100), Owner varchar(100))
Create table #TmpTableSec2 (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
Create table #TmpResult (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
DECLARE DBCURSOR CURSOR FOR
select name 
from sys.databases where state=0 and name not in ('tempdb','model','master','msdb')
Declare @name varchar(50)
Declare @cmd  varchar(200)
Declare @dbowner varchar(100)
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
      --print 'Database --> ['+@name+']'
      set @cmd = 'select name,  suser_sname(owner_sid) from master.sys.databases where name = '''+@name+''''
      --select @cmd
      insert #TmpTableSec1 exec (@cmd)
      --select @dbowner = (select suser_sname(owner_sid) from master.sys.databases where name = @name)
      set @cmd = 'use '+ @name +'
      select db_name(), name, suser_sname(sid)
      from sys.database_principals where name = ''dbo'''
      --select ''@DBO'' = (select suser_sname(sid) from sys.database_principals where name = ''dbo'') '
      INSERT #TmpTableSec2 exec (@cmd) 
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
--select * from #TmpTableSec1
--select * from #TmpTableSec2
insert into #TmpResult
select a.database_name, a.Owner, b.DBO_Owner_Login from #TmpTableSec1 a
join #TmpTableSec2 b
on a.database_name = b.database_name
--select * from #TmpResult

Create table #GroupMemberTable (account_name varchar(200), type varchar(50), privilege varchar(100), login_name varchar(200), group_name varchar(500))

DECLARE DBCURSOR2 CURSOR FOR
select distinct(DBO_Owner_Login)
from #TmpResult where DBO_Owner_Login like '%\%' and DBO_Owner_Login <> 'NT AUTHORITY\SYSTEM'
Declare @DBO varchar (200)
Declare @DBNAME varchar (200)
OPEN DBCURSOR2
FETCH NEXT FROM DBCURSOR2 INTO @DBO
WHILE @@FETCH_STATUS = 0
BEGIN
      set @cmd = 'EXEC xp_logininfo '''+@DBO+ ''', ''all'' '
      insert into #GroupMemberTable EXEC (@cmd)
      --update #GroupMemberTable set database_name = @DBNAME where id=@@IDENTITY 
      --print @cmd
FETCH NEXT FROM DBCURSOR2 INTO @DBO
END
CLOSE DBCURSOR2
DEALLOCATE DBCURSOR2
--select * from #TmpResult
--select * from #GroupMemberTable where group_name <> 'NULL' --and privilege = 'admin'

Create table #Tmp_DB_ownerRoleResults (database_name varchar(100), DBOwnerRole_Member_UserName varchar(200), User_type varchar(100))
DECLARE DBCURSOR3 CURSOR FOR
select database_name
from #TmpResult where DBO_Owner_Login <> 'NT AUTHORITY\SYSTEM'
Declare @database_name3 varchar (200)
Declare @cmd3  varchar(500)
OPEN DBCURSOR3
FETCH NEXT FROM DBCURSOR3 INTO @database_name3
WHILE @@FETCH_STATUS = 0
BEGIN
      set @cmd3 = 'use '+ @database_name3 +'
      select db_name(), user_name(member_principal_id), type_desc from sys.database_role_members a
      inner join sys.database_principals b
      on a.member_principal_id = b.principal_id where a.role_principal_id=16384'
      --print @cmd3
      --exec (@cmd3)
      insert into #Tmp_DB_ownerRoleResults exec (@cmd3)
FETCH NEXT FROM DBCURSOR3 INTO @database_name3
END
CLOSE DBCURSOR3
DEALLOCATE DBCURSOR3

print '###########################################################'
print '###### Database Owner and DBO Owner Logging Mapping #######'
print '###########################################################'
select * from #TmpResult


print '###########################################################################'
print '###### AD Group Member Logins which have DBO Access in the database #######'
print '###########################################################################'
create table #TmpTableSec3 (database_name varchar(100), Database_Owner varchar(200), DBO_Owner_Login varchar(200), type varchar (100), privilege varchar(100), group_name varchar(500))
insert into #TmpTableSec3
select a.database_name, a.principal, a.DBO_Owner_Login, b.type, b.privilege, b.group_name
from #TmpResult a
left join #GroupMemberTable b
on a.DBO_Owner_Login = b.login_name
where b.group_name is not null --and b.privilege = 'admin'
order by a.database_name
select database_name, DBO_Owner_Login, privilege, group_name  from #TmpTableSec3 
where privilege = 'admin' order by database_name asc

print '####################################################################################'
print '###### Members of DB_OWNER ROLE For Each Database (Excluding DBO Owner Login)#######'
print '####################################################################################'
Create table #TmpTableSec4 (database_name varchar(100), DBOwnerRole_Member_UserName varchar(200), User_type varchar(100))
insert into #TmpTableSec4
select a.database_name as [Database_Name], b.DBOwnerRole_Member_UserName, b.User_type 
from #TmpResult a
inner join #Tmp_DB_ownerRoleResults b
on a.database_name = b.database_name
where b.DBOwnerRole_Member_UserName <> 'dbo'

Create table #TmpTableSec5 (database_name varchar(100), DBOwnerRole_Member_UserName varchar(200), User_type varchar(100))
insert into #TmpTableSec5
select a.name as [Database_Name], b.DBOwnerRole_Member_UserName, b.User_type 
from sys.databases a
left outer join #TmpTableSec4 b
on a.name = b.database_name

select * from #TmpTableSec5
where DBOwnerRole_Member_UserName is not null
order by database_name

print''
print '#########################################################################################################################'
print '###### Final Result: Users who are not part of DB_OWNER Role nor having explicit login access but have DBO rights #######'
print '#########################################################################################################################'
print ''
create table #TmpFinalResult (group_name varchar(500), database_name varchar(100), DBO_Owner_Login varchar(200), Database_Owner varchar(200), privilege varchar(100))
insert into #TmpFinalResult
select distinct (a.group_name), a.database_name, a.DBO_Owner_Login, a.Database_Owner, a.privilege
from #TmpTableSec3 a
where a.database_name in (select database_name from #TmpResult where principal <> DBO_Owner_Login)
and a.group_name not in (select name from sys.syslogins where sysadmin = 1 and isntgroup = 1)
and a.DBO_Owner_Login not in (select name from sys.syslogins where sysadmin = 1 and isntuser = 1)
and a.DBO_Owner_Login not in (select DBO_Owner_Login from #TmpTableSec3 where privilege = 'admin')
--and a.group_name not in ('Any Domain Groups to be filtered goes here e.g. [DOMAIN\All Users]')
order by a.database_name asc
select a.database_name, a.DBO_Owner_Login, a.Database_Owner, a.group_name, a.privilege from #TmpFinalResult a
order by a.database_name asc

drop table #TmpTableSec1
drop table #TmpTableSec2
drop table #TmpTableSec3
drop table #TmpTableSec4
drop table #TmpTableSec5
drop table #TmpResult
drop table #GroupMemberTable
drop table #Tmp_DB_ownerRoleResults
drop table #TmpFinalResult
go
set nocount off
go

Explanation of the Script Output

###########################################################

###### Database Owner and DBO Owner Logging Mapping #######

###########################################################

This section gives you the mapping between the database owner in sys.databases and the login which owns the DBO User in each database. If you move databases between servers, its possible that the old DBO Login doesn’t exist on the new instance and this could cause unauthorized access by the old DBO id he/she has login access to the server, then he/she retains DBO access in the newly moved database.

###########################################################################

###### AD Group Member Logins which have DBO Access in the database #######

###########################################################################

This section presents to you the different Active Directory Groups that have DBO/DB_Owner access in each database. Sometimes, if you have too many AD Groups in different OU’s, for a DBA it gets confusing to manage the authorized access. You can certainly manually look up the AD Users and figure this out. But, being a SQL person I’m sure its a lot more comfortable seeing this within SQL Server itself. This script does just that and shows the diff groups per each database.

####################################################################################

###### Members of DB_OWNER ROLE For Each Database (Excluding DBO Owner Login)#######

####################################################################################

Since we are anyways getting DBO Owner login we might as well detect the different users who are part of DB_OWNER fixed database role. Each member of DB_OWNER role has Full Control on the database. Hence the need to track this information.

#########################################################################################################################

###### Final Result: Users who are not part of DB_OWNER Role nor having explicit login access but have DBO rights #######

#########################################################################################################################

This is the one that does the magic! It gives a final report of each user who has DBO Access, but who isn’t a part of DB_OWNER role nor does this person has sysadmin through any of the AD Groups. It also reports all the AD Group, through which this windows user can get a login access to SQL Server thereby gaining DBO Access when switching context to the database reported against the user’s name. If you see any results here, then its very much a security hole, that needs to be plugged after careful review of this user’s individual object rights.

The commented line on a.group_name in the final section, is for you to add any AD Groups which each user in the organisation is a part of, thereby removing any unnecessary rows from the output.

Have fun Auditing. Please post back your comments on the security script…