Script to examine SQL Server 2000 logins

-- sp_logins_report.sql

use dba

drop proc sp_logins_report

create proc sp_logins_report

-- to report server-roles & database-roles per login, RbS 09Feb2011
-- eg: exec dba..sp_logins_report

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

--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)

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

--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
set @counter = @@rowcount

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

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


--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_dbname DBName,
re_rolename RoleName, re_servername ServerName
from #report
where re_username 'dbo'
and re_username not like 'CORPIT%'
and re_username not like ('%svc_%')
order by re_username --display by user
--order by re_rolename --display by role
--order by re_dbname --display by database



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s