Script to examine SQL Server 2005/08 logins

Adapting my previous “Script to examine SQL Server 2000 logins” for an upcoming SQL-2005 audit 🙂 …

-- sp_logins_report.sql
use dba
go

begin try drop table #rolemember, #dbnames, #report END TRY
BEGIN CATCH END CATCH -- ignore errors

--variables
declare @counter int
declare @dbname varchar(50)
declare @sqlstr nvarchar(4000)

--make table to hold database, user-define roles & user-names
create table #rolemember
(
rm_servername varchar(50) default @@servername,
rm_dbname varchar(1000),
rm_rolename varchar(1000),
rm_username varchar(1000),
rm_userid varchar(1000)
)

--make table to hold database names
create table #dbnames
(dbid int identity(1,1),
db_dbname varchar(50))

--make table to accumulate report
create table #report
(
re_servername varchar(50),
re_dbname varchar(1000),
re_rolename varchar(1000),
re_username varchar(1000),
re_userid varchar(1000)
)

--get members of each server role
insert into #rolemember (rm_rolename, rm_username, rm_userid)
exec dbo.sp_helpsrvrolemember

--get database names
insert into #dbnames (db_dbname)
select '[' + name + ']' from master.dbo.sysdatabases
where version > 0  -- online
set @counter = @@rowcount

--loop through databases to get members of database roles and user-defined roles
while @counter > 0
	begin

	--get database name from #dbnames table
	set @dbname = (select db_dbname from #dbnames where dbid = @counter)

	--get members of each database and user-defined role
	set @sqlstr = 'insert into #rolemember (rm_rolename, rm_username, rm_userid)
	exec ' + @dbname + '.dbo.sp_helprolemember'
	exec sp_executesql @sqlstr

	--update database name in rolemember table
	update #rolemember
	set rm_dbname = @dbname
	where rm_dbname is null

	set @counter = @counter - 1
	end

--put data into report table
insert into #report
select rm.* from #rolemember rm
left join #report re
on rm.rm_username = re.re_username
and rm.rm_dbname = re.re_dbname
and rm.rm_rolename = re.re_rolename
and rm.rm_servername = re.re_servername
where re.re_servername is null

--display report
select re_username UserName, re_rolename RoleName, re_dbname DBName, re_servername ServerName
from #report
where re_username != 'dbo'
--order by re_username --display by user
order by re_rolename --display by role
--order by re_dbname --display by database