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
Database Consultant 