Monday, January 21, 2008

Querying SQL 2000 Table Permissions

Since I am auditing old sql 2000 instances, these methods are necessary

Method 1 : The appropriate information_schema view.


select
grantor,
grantee,
table_schema+'.'+table_name as objectname,
privilege_type
from
information_schema.table_privileges



Method 2 : Capturing the output of sp_helpprotect
(nb : sp_helpprotect can be passed parameters - http://msdn2.microsoft.com/en-us/library/ms190310.aspx)


create table #helpprotect_results (
owner sysname,
object sysname,
grantee sysname,
grantor sysname,
protecttype nvarchar(10),
action nvarchar(20),
columnname sysname )

insert into #helpprotect_results exec sp_helprotect

select grantor,
grantee,
owner + '.' + object as objectname,
protecttype,
action
from #helpprotect_results
order by grantee

drop table #helpprotect_results



Method 3 : Good old system tables


select
user_name(sec.grantor) as grantor,
user_name(sec.uid) as grantee,
stbl.name + '.' + obj.name as objectname,
case obj.type
when 'C' then 'Check constraint'
when 'D' then 'Default (constraint or stand-alone)'
when 'F' then 'Foreign Key constraint'
when 'PK' then 'Primary Key constraint'
when 'P' then 'SQL Stored procedure'
when 'FN' then 'SQL scalar function'
when 'R' then 'Rule (old-style, stand-alone)'
when 'RF' then 'Replication-filter-procedure'
when 'S' then 'System base table'
when 'TA' then 'assembly (CLR) DML trigger'
when 'TF' then 'SQL table-valued-function'
when 'U' then 'Table (user-defined)'
when 'UQ' then 'Unique constraint'
when 'V' then 'View'
when 'X' then 'Extended stored procedure'
end as objtype,
protecttype.name permission_level,
action.name as permission
from
dbo.sysobjects as obj
inner join sysusers as stbl on stbl.uid = obj.uid
inner join sysprotects as sec on sec.id = obj.id
inner join master.dbo.spt_values as action on sec.action = action.number and action.type = 't'
inner join master.dbo.spt_values as protecttype on sec.protecttype = protecttype.number and protecttype.type = 't'
where
obj.type in ('U','V','P','FN','X')

No comments: