Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
reddy_vam
Starting Member
43 Posts |
Posted - 2008-12-14 : 13:10:59
|
Hi,How can i list the DDL Statement permissions for all database users.Thanks,Reddy |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-14 : 13:24:48
|
See my script:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111918 |
|
|
reddy_vam
Starting Member
43 Posts |
Posted - 2008-12-14 : 23:00:09
|
Thanks Sodeep,Regrads,Reddy |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-14 : 23:22:13
|
Glad it was helpful. |
|
|
reddy_vam
Starting Member
43 Posts |
Posted - 2008-12-16 : 10:11:52
|
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 intdeclare @Users_In_Group varchar(200)declare @UserId intIF OBJECT_ID('tempdb..#temp30') IS NOT NULL DROP TABLE #temp30create 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 #temp35create table #temp35(DBName Varchar(100),Group_Name Varchar(100),Group_Id int, Users_In_Group varchar(200),UserId int) declare db_cursor cursor forselect name from sys.databasesopen db_cursorfetch next from db_cursor into @DBNamewhile (@@fetch_status=0)begininsert #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_cursorfetch next from db_cursor into @DBNameendselect DBName,Group_Name,Users_in_Group from #temp35 order by DBNameclose db_cursordeallocate db_cursor |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-16 : 10:19:21
|
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 intdeclare @Users_In_Group varchar(200)declare @UserId intIF OBJECT_ID('tempdb..#temp30') IS NOT NULL DROP TABLE #temp30create 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 #temp35create table #temp35(DBName Varchar(100),Group_Name Varchar(100),Group_Id int, Users_In_Group varchar(200),UserId int) declare db_cursor cursor forselect name from sys.databasesopen db_cursorfetch next from db_cursor into @DBNamewhile (@@fetch_status=0)begininsert #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_cursorfetch next from db_cursor into @DBNameendselect DBName,Group_Name,Users_in_Group from #temp35 order by DBNameclose db_cursordeallocate db_cursor
What happened with my script? Why cursors? |
|
|
reddy_vam
Starting Member
43 Posts |
Posted - 2008-12-16 : 11:35:33
|
Hi Sodeep,Your script was working fine...but i am just curious about why my script was giving the wrong database names...where am i missing...Thanks,Reddy |
|
|
|
|
|
|
|