SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
alter procedure spDatabaseRoles
@strServer Varchar(100),
@strDatabase Varchar(50)='master',
@login  Varchar(100)=null,
@password Varchar(100)=null
/*

spDatabaseRoles 'MyServer', 'MyDatabase', 'MyUserID', 'MyPassword'
						--SQL Server authentication
spDatabaseRoles 'MyServer', 'MyDatabase'--log in via windows authentication

This provides a table giving a list of the database roles and the
users assigned to them, for a particular database


*/
as
declare @hr int,		--the HRESULT returned from the OLE operation
	@ii int,		--a simple counter
	@jj int,		--a simple counter
	@kk int,		--a simple counter
	@ll int,		--a simple counter
	@iimax int,		--the max of the iteration
	@objServer int,		--the Server object
	@objDatabaseRoles int,	--the Database roles object
	@objQueryResults int,	--the QueryResults object
	@ErrorObject int,	--the error object
	@ErrorMessage varchar(255),--the potential error message
	@Command varchar(8000),
	@ResultSetCount int,
	@CurrentResultSet int,
	@cols int,
	@Rows int,
	@Colname varchar(100),
	@strCurrentRow varchar(8000),
	@string varchar(8000),
	@Approle varchar(255),
	@RoleName varchar(255)

Declare @members table (Role_ID int, RoleName varchar(50),appRole Varchar(10),Member varchar(50))
set nocount on

exec @hr = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT

if @password is null or @login is null
	begin
	--use a trusted connection 
	if @hr=0 Select @ErrorMessage='Setting login to windows authentication on '+@strServer,@ErrorObject=@objServer
	if @hr=0 exec @hr = sp_OASetProperty @objServer, 'LoginSecure', 1
	if @hr=0 Select @ErrorMessage='logging in to the requested server using windows authentication on '+@strServer
	if @login is null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer 
	if @login is not null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer ,@Login
	end
else
	begin
	Select @ErrorMessage='logging in to the requested server using SQL Server authentication to '+@strServer,
		@ErrorObject=@objServer
	if @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer ,
						 @login , @password
	end

if @hr=0 Select @ErrorMessage='getting database roles from '+@strServer,
	@ErrorObject =@objServer,@command='Databases('+@strDatabase+').DatabaseRoles'
if @hr=0 exec @hr = sp_OAGetProperty @objServer, @command, @objDatabaseRoles OUT

if @hr=0 Select @ErrorMessage='getting count of database roles from '+@strServer,
	@ErrorObject =@objDatabaseRoles
if @HR=0  EXEC @hr = sp_OAGetProperty @objDatabaseRoles, 'Count', @iimax OUT

if @HR=0  Select @errorMessage='Getting each item',@ii=1
while @hr=0 and @ii<=@iiMax
	begin
	--Set oQueryResults =oDatabaseRole.EnumDatabaseRoleMember 
	if @hr=0 select @command='item('+cast (@ii as varchar)+').AppRole',
		@errorMessage='Getting the Approle',
		@ErrorObject =@objDatabaseRoles
	if @hr=0 EXEC @hr = sp_OAGetProperty @objDatabaseRoles, @command, 
		@Approle OUT
	if @hr=0 select @command='item('+cast (@ii as varchar)+').Name',
		@errorMessage='Getting the name'
	if @hr=0 EXEC @hr = sp_OAGetProperty @objDatabaseRoles, @command, 
		@RoleName OUT
	if @hr=0 select @command='item('+cast (@ii as varchar)+').EnumDatabaseRoleMember',
		@errorMessage='Getting the EnumDatabaseRoleMember'
	if @hr=0 EXEC @hr = sp_OAGetProperty @objDatabaseRoles, @command, 
		@objQueryResults OUT
	if @hr=0 Select @ErrorMessage='getting resultSetCount '+@strServer,
		@ErrorObject =@objQueryResults
	if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, 'ResultSets', @ResultSetCount OUT
	insert into @members(role_ID, RoleName, appRole, Member)
		Select @ii, @RoleName,@AppRole, ''
	Select @jj=1
	while @hr=0 and @jj<=@ResultSetCount
		begin
		if @hr=0 Select @ErrorMessage='getting column count'
		if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, 'Columns', @cols OUT
		if @hr=0 Select @ErrorMessage='getting row count'
		if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, 'Rows', @rows OUT
		if @Rows>0
			begin
			delete from @members where role_ID=@ii
			--get the result set headings
			Select @kk=1, @strCurrentRow=''
			while @kk<=@cols and @hr=0
				begin
				select @command='ColumnName('+cast(@kk as varchar(2))+')'
				if @hr=0 Select @ErrorMessage='getting '+@command
				if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, @command, @colname OUT
				select @strCurrentRow=@strCurrentRow+@colName
				Select @kk=@kk+1
				end
			Select @kk=1, @ll=1
			while @kk<=@rows
				begin
				select @strCurrentRow=''
				while @ll<=@cols
					begin
					select @command='GetColumnString('+cast(@kk as varchar(2))+', '
							+cast(@ll as varchar(2))+')'
					if @hr=0 Select @ErrorMessage='doing '+@command
					if @hr=0 exec @hr = sp_OAMethod @objQueryResults, @command, @string OUT
					select @strCurrentRow=@strCurrentRow+@string
					Select @ll=@ll+1
					end
				insert into @members(role_ID, RoleName, appRole, Member)
					Select @ii, @RoleName,@AppRole, @strCurrentRow+' ('+@colname+')'
				Select @kk=@kk+1,@ll=1
				end
			end
		Select @jj=@jj+1
		end
	Select @ii=@ii+1
	end
Select * from @Members
--and handling any errors
if @hr<>0
	begin
	Declare 
		@Source varchar(255),
		@Description Varchar(255),
		@Helpfile Varchar(255),
		@HelpID int
	
	EXECUTE sp_OAGetErrorInfo  @errorObject, 
		@source output,@Description output,@Helpfile output,@HelpID output

	Select @ErrorMessage='Error whilst '+@ErrorMessage+', '+@Description
	raiserror (@ErrorMessage,16,1)
	end
exec sp_OADestroy @objDatabaseRoles	--the Database roles object
exec sp_OADestroy @objQueryResults	--the QueryResults object
exec sp_OADestroy @objServer

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO