quote:

---

Originally posted by reddy_vam

Hi Folks,

I have used the following sccript to retrieve all Database users who are having DDL statement permissions, but somehow this was giving me incorrect database names, can somebody please let me know where the error is?

declare @DBName varchar(100)
declare @Group_Name varchar(100)
declare @Group_Id int
declare @Users_In_Group varchar(200)
declare @UserId int
IF OBJECT_ID('tempdb..#temp30') IS NOT NULL
DROP TABLE #temp30
create table #temp30(Group_Name Varchar(100),Group_Id int,
Users_In_Group varchar(200),UserId int)

IF OBJECT_ID('tempdb..#temp35') IS NOT NULL
DROP TABLE #temp35
create table #temp35(DBName Varchar(100),Group_Name Varchar(100),Group_Id int,
Users_In_Group varchar(200),UserId int)
declare db_cursor cursor for
select name from sys.databases
open db_cursor
fetch next from db_cursor into @DBName
while (@@fetch_status=0)
begin
insert #temp30 exec (@DBName +'..sp_helpuser ''DB_DDLadmin''')
declare temp_cursor cursor for
select * from #temp30
open temp_cursor
fetch next from temp_cursor into @Group_Name,@Group_Id,@Users_In_Group,@UserId
while (@@fetch_status=0)
Begin
insert into #temp35 values(@DBName,@Group_Name,@Group_Id,@Users_In_Group,@UserId)
fetch next from temp_cursor into @Group_Name,@Group_Id,@Users_In_Group,@UserId
end
close temp_cursor
deallocate temp_cursor
fetch next from db_cursor into @DBName
end
select DBName,Group_Name,Users_in_Group from #temp35 order by DBName
close db_cursor
deallocate db_cursor

---