March 28, 2003 at 9:42 am
I wanted a SQL statment which will display the permissions of all the tables in the Database.
Thanks in advance.
March 28, 2003 at 10:47 am
You could create a cursor like this:
DECLARE @Table AS SYSNAME
DECLARE curTables CURSOR LOCAL FOR
SELECT [name] FROM sysobjects WHERE xtype = 'U'
OPEN curTables
FETCH NEXT FROM curTables INTO @Table
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'Permissions for Table ' + @Table
EXEC (' EXEC sp_table_privileges ' + @Table)
PRINT ''
FETCH NEXT FROM curTables INTO @Table
END
CLOSE curTables
DEALLOCATE CurTables
March 28, 2003 at 11:43 am
No cursor, no stored procedure:
select
convert(sysname,db_name()) TABLE_QUALIFIER,
convert(sysname,user_name(o.uid)) TABLE_OWNER,
convert(sysname,object_name(o.id)) TABLE_NAME,
convert(sysname,user_name(p.grantor)) GRANTOR,
convert(sysname,user_name(u.uid)) GRANTEE,
convert(varchar(32),case p.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert(varchar(3),case when p.protecttype = 205 then 'NO'
else 'YES'
end) IS_GRANTABLE
from sysprotects p, sysobjects o, sysusers u, sysmembers m
where
p.id = o.id
and o.type in ('U','V','S')
and (u.uid > 0 and u.uid < 16384)
and ((p.uid = u.uid) or
(p.uid = m.groupuid and u.uid = m.memberuid))
and p.protecttype <> 206/* only grant rows */
and p.action in (26,193,195,196,197)
and o.uid <> u.uid/* no rows for owner */
and not exists (/* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = p.action
and p1.id = p.id
and p1.uid = u.uid)
union
select/*Add rows for table owner */
convert(sysname,db_name()) TABLE_QUALIFIER,
convert(sysname,user_name(o.uid)) TABLE_OWNER,
convert(sysname,object_name(o.id)) TABLE_NAME,
convert(sysname,user_name(u.uid)) GRANTOR,
convert(sysname,user_name(o.uid)) GRANTEE,
convert(varchar(32),case v.number
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert(varchar(3),'YES') IS_GRANTABLE
from sysobjects o, master.dbo.spt_values v, sysusers u
whereo.type in ('U','V','S')
and u.uid = 1/* grantor is 'dbo' of database */
and v.type = N'P'/* cross product to get all exposed privileges */
and v.number in (26,193,195,196,197)
and not exists (/* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = v.number
and p1.id = o.id
and p1.uid = o.uid)
order by 2,3,6,5
March 28, 2003 at 12:16 pm
Thanks racosta for you help
Thanks mromm, I saw the same code in the sp_table_privileges.
March 29, 2003 at 1:46 am
The code presented by mromm is very nice. You can also take a look at the PERMISSIONS function.
SELECT PERMISSIONS(OBJECT_ID('Orders'))
Look up the bitmask of possible values in BOL.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply