quote:

---

Originally posted by sharona

I have the following procedure where i would like to
run this together as one script. right now i have to run the create table, then the select. if i put he create procedure under the create table i get an error. Any ideas?

Create procedure dbo.Get_List_of_dbroles
AS

BEGIN

CREATE TABLE dbo.DBROLES
( DBName sysname not null,
USERid varchar (255) not null,
dataextractagent varchar(3) not null,
db_accessadmin varchar(3) not null,
db_backupoperator varchar(3) not null,
db_datareader varchar(3) not null,
db_datawriter varchar(3) not null,
db_ddladmin varchar(3) not null,
db_denydatareader varchar(3) not null,
db_denydatawriter varchar(3) not null,
db_owner varchar(3) not null,
db_securityadmin varchar(3) not null,
External_Client_Access varchar(3) not null,
Internal_User_Access varchar(3) not null,
IT_Select_Access varchar(3) not null,
IT_Support_Access varchar(3) not null,
QSRProdSupport varchar(3) not null,
Cur_Date datetime not null default getdate())
GO
---------------------------------------------------
declare @dbname varchar(200)
declare @mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb','BarredEnsisUsers','EnSIS_27Jul2011_CR6136_TARP',
'JobMonitor','LiteSpeedLocal','WebDBEx','model','QSRChangeManagement')--,'EnSIS_02Mar2011_SC')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into DBROLES ( DBName,USERid,dataextractagent,
db_accessadmin,db_backupoperator,db_datareader,db_datawriter,db_ddladmin,db_denydatareader,
db_denydatawriter,db_owner,db_securityadmin,External_Client_Access,Internal_User_Access,
IT_Select_Access,IT_Support_Access,QSRProdSupport)

SELECT '+''''+@dbName +''''+ ' as DBName ,USERid, '+char(13)+ '
Max(CASE RoleName WHEN ''dataextractagent'' THEN ''Yes'' ELSE ''No'' END) AS dataextractagent,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_backupoperator'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter,
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''External_Client_Access'' THEN ''Yes'' ELSE ''No'' END) AS External_Client_Access,
Max(CASE RoleName WHEN ''Internal_User_Access'' THEN ''Yes'' ELSE ''No'' END) AS Internal_User_Access,
Max(CASE RoleName WHEN ''IT_Select_Access'' THEN ''Yes'' ELSE ''No'' END) AS IT_Select_Access,
Max(CASE RoleName WHEN ''IT_Support_Access'' THEN ''Yes'' ELSE ''No'' END) AS IT_Support_Access,
Max(CASE RoleName WHEN ''QSRProdSupport'' THEN ''Yes'' ELSE ''No'' END) AS QSRProdSupport

from (
select b.name as USERid, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERid
order by USERid'

Execute (@mSql1)

FETCH NEXT FROM DBName_Cursor INTO @dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go/**************************************/

SELECT
dbo.DBROLES.USERid,
dbo.Users.Name,
dbo.Users.ActiveFlag,
dbo.DBROLES.DBName,
dbo.DBROLES.dataextractagent,
dbo.DBROLES.db_accessadmin,
dbo.DBROLES.db_backupoperator,
dbo.DBROLES.db_datareader,
dbo.DBROLES.db_datawriter,
dbo.DBROLES.db_ddladmin,
dbo.DBROLES.db_denydatareader,
dbo.DBROLES.db_denydatawriter,
dbo.DBROLES.db_owner,
dbo.DBROLES.db_securityadmin,
dbo.DBROLES.External_Client_Access,
dbo.DBROLES.Internal_User_Access,
dbo.DBROLES.IT_Select_Access,
dbo.DBROLES.IT_Support_Access,
dbo.DBROLES.QSRProdSupport,
dbo.DBROLES.Cur_Date
FROM
dbo.DBROLES
left outer jOIN
dbo.Users ON dbo.DBROLES.USERid = dbo.Users.SQLUserID

---