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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-19 : 11:22:53
|
Manikandan submitted "This is one sql which list all the objects with its users and his corresponding permission. (System objects are excluded)
declare db_objects_list cursor for select name from master..sysdatabases
declare @db_name varchar(100)
declare @table varchar(100)
open db_objects_list
fetch next from db_objects_list into @db_name
WHile (@@fetch_status = 0 )
begin
exec ('declare curobjlists cursor for select '+@db_name+ '..sysprotects.id,action,'+@db_name+'..sysprotects.uid,'+@db_name+'..sysusers.name,'+@db_name+'..sysobjects.name from ' +@db_name+ '.. sysprotects,'+@db_name+'..sysusers,'+@db_name+'..sysobjects where '+@db_name+'..sysprotects.uid = '+@db_name+'..sysusers.uid and '+@db_name+'..sysprotects.id = '+@db_name+'..sysobjects.id and '+@db_name+'..sysobjects.type <> ''S''')
open curobjlists
declare @a1 varchar(50)
declare @a2 varchar(50)
declare @a3 varchar(50)
declare @a4 varchar(50)
declare @a5 varchar(50)
create table tempstore
(
a1 varchar(50),
permission varchar(50),
a3 varchar(50),
username varchar(50),
objname varchar (50)
)
fetch next from curobjlists into @a1,@a2,@a3,@a4,@a5
WHILE (@@fetch_status = 0)
begin
select @a2 = name from master..spt_values where number = @a2 and type = 'T'
insert into tempstore values (@a1,@a2,@a3,@a4,@a5)
fetch next from curobjlists into @a1,@a2,@a3,@a4,@a5
end
select @db_name,objname,username,permission from tempstore
close curobjlists
drop table tempstore
deallocate curobjlists
fetch next from db_objects_list into @db_name
end
close db_objects_list
deallocate db_objects_list" |
|
|
|
|
|
|
|
SQL to list objects