Doing some security cleanup. This script is useful. Ran it against all servers in the environment and needed to fix it up a little to work. Finds members of db_owner,db_accessadmin. However, I don’t think it elaborates on what login dbo is mapped to. I’ve included a script below that checks for that – also tweaked to fix.
DECLARE @databaseName SYSNAME ,
@sql VARCHAR(1000)
DECLARE @Tbl TABLE
(
DatabaseName VARCHAR(200) ,
RoleDesc VARCHAR(100) ,
UserName VARCHAR(100)
)
DECLARE databaseCursor CURSOR
FOR
SELECT Name
FROM sys.databases
WHERE state = 0
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @databaseName
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = 'SELECT ''' + @databaseName + ''' AS [Database],
USER_NAME(role_principal_id) AS [Role],
USER_NAME(member_principal_id) AS [User]
FROM [' + @databaseName
+ '].sys.database_role_members
WHERE USER_NAME(role_principal_id) IN (''db_owner'', ''db_accessadmin'') and USER_NAME(member_principal_id) <> ''dbo'' '
INSERT INTO @Tbl
EXEC ( @sql
)
FETCH NEXT FROM databaseCursor INTO @databaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
SELECT *
FROM @Tbl
dbo login mapping:
--Script : Verify the database owner and DBO User owner are the same
--When a login is defined as the DBO user, he/she has FULL control within the database
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')
Declare @name varchar(100)
Declare @cmd varchar(200)
Declare @dbowner varchar(100)
Declare @DBO 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)
--print ''
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
go
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
print '--- Database Owner & DBO User Mapping ---'
print '*****************************************'
select * from #TmpResult
print '--- Orphaned DBO Logins ---'
print '***************************'
select * from #TmpResult
where DBO_Owner_Login not in (select name from sys.server_principals) or DBO_Owner_Login is null
drop table #TmpTableSec1
drop table #TmpTableSec2
drop table #TmpResult
set nocount off
Thanks to the sqldude and msft forum members for providing these nearly complete scripts:
Verifying DBO User ownership and track users without a SQL login but via AD Group Logins