April 17, 2012

sp_column_privileges (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_column_privileges(nvarchar @table_name
, nvarchar @table_owner
, nvarchar @table_qualifier
, nvarchar @column_name)

MetaData:

   
create procedure sys.sp_column_privileges
(
@table_name sysname, -- no pattern matching
@table_owner sysname = null, -- no pattern matching
@table_qualifier sysname = null, -- no pattern matching
@column_name nvarchar(384) = null -- SUPPORTS PATTERN MATCHING
)
as
declare @table_id int

if @column_name is null -- If column name not supplied, match all
select @column_name = '%'

if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end

if @table_owner is null
begin -- If unqualified table name
select @table_id = object_id(quotename(@table_name))
end
else
begin -- Qualified table name
if @table_owner = N''
begin -- If empty owner name
select @table_id = 0
end
else
begin
select @table_id = object_id(quotename(@table_owner) + '.' + quotename(@table_name))
end
end

select
TABLE_QUALIFIER = convert(sysname,db_name()),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = @table_name,
COLUMN_NAME = convert(sysname,c.name),
GRANTOR = user_name(p.grantor_principal_id),
GRANTEE = user_name(u.principal_id),
PRIVILEGE = convert (varchar(32),
case p.type
when 'SL' then 'SELECT'
when 'IN' then 'INSERT'
when 'DL' then 'DELETE'
when 'UP' then 'UPDATE'
else 'REFERENCES'
end),
IS_GRANTABLE = convert (varchar(3), case when p.state = 'G' then 'NO' else 'YES' end)
from
sys.all_objects o inner join
sys.all_columns c on
(
c.object_id = @table_id and
c.object_id = o.object_id and
c.name like @column_name
) inner join
sys.database_permissions p on
(
p.major_id = c.object_id and
p.class = 1 and
p.state in ('G','W') and
(p.minor_id = 0 or p.minor_id = c.column_id)
) inner join
sys.database_principals u on
(
u.principal_id = p.grantee_principal_id
)
where
not exists ( -- exclude revoke'd privileges
select *
from
sys.database_permissions p1
where
p1.class = 1 and
p1.major_id = p.major_id and
p1.minor_id = c.column_id and
p1.state in ('D', 'R') and
p1.type = p.type and
p1.grantee_principal_id = p.grantee_principal_id and
p1.grantor_principal_id = p.grantor_principal_id
)
union

select -- Add rows for table owner
TABLE_QUALIFIER = convert(sysname,db_name()),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = @table_name,
COLUMN_NAME = convert(sysname,col_name(@table_id, c.column_id)),
GRANTOR = convert(sysname,user_name(u.principal_id)),
GRANTEE = convert(sysname,user_name(ObjectProperty(o.object_id,'ownerid'))),
PRIVILEGE = convert (varchar(32), v.permission),
IS_GRANTABLE = convert(varchar(3),'YES')
from
sys.all_objects o inner join
sys.all_columns c on
(
c.object_id = @table_id and
c.object_id = o.object_id and
c.name like @column_name
) inner join
sys.database_principals u on
(
u.principal_id = 1 -- grantor is 'dbo' of database
) inner join
sys.spt_permission_names v on
(
v.number > 0 and -- exclude delete
has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name), 'OBJECT', v.permission) = 1
)

order by 4, 7

No comments:

Post a Comment

Total Pageviews