Monday 21 January 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: