Category Archives: TSQL

Tweaked script to find all db_owner members

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:

http://thesqldude.com/2012/02/18/verifying-dbo-user-ownership-and-track-users-without-a-sql-login-but-via-ad-group-logins/

Running a plethora of T-SQL

Have you ever needed to run a bunch of scripts, over and over and over? And the SSMS tabs – it can be a mess. This is mind-numbing work – call in the computers. Check out this script I forked from GitHub and modified: https://github.com/propellor/Powershell-pasen/blob/master/Run-Sql.ps1

You point the script it at a folder of scripts and it will fire them off using invoke-sqlcmd. You can use it in a sort-of debugging mode, to get those scripts all working when you get a mess from a co-worker or vendor and have it pick up rerunning from the failed script. Alternatively, if someone else is responsible for the fixin’ and you are just the runner, you can have all the failed scripts rename themselves to .failure.

Also, my first GitHub fork.

UPDATE: Since doing this, I’ve started using RoundhousE, which does the same thing, but waaaay better. Using TFS and octopus deploy, we package up roundhouse with some sql scripts and let developers deploy database scripts to staging and then production.