May 24, 2012

sp_MSobjectprivs (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_MSobjectprivs(nvarchar @objname
, nvarchar @mode
, int @objid
, int @srvpriv
, int @prottype
, nvarchar @grantee
, int @flags
, int @rollup)

MetaData:

   
create proc sys.sp_MSobjectprivs
@objname nvarchar(776) = null,
@mode nvarchar(10) = N'object',
@objid int = null,
@srvpriv int = null,
@prottype int = null,
@grantee nvarchar(258) = null,
@flags int = 0,
@rollup int = 0
as

create table #objs(
id int NOT NULL
)

-- Temp table will hold output for final select --
create table #output (
action int NOT NULL,
colid int NULL,
uid int NOT NULL,
protecttype int NOT NULL,
id int NOT NULL,
grantor int
)

create table #tmp(
action int NOT NULL,
uid int NOT NULL,
protecttype int NOT NULL,
)

-- mode : 'object', 'user' or 'column' --
--
* Note: This was expanded for 6.5 due to changes in sysprotects.columns usage, affecting
* CPermission::ListPrivilegeColumns. The following additional parameters are for this.
--
-- objid : ID of the object we're querying --
-- srvpriv : privilege that we're querying for (e.g. select) --
-- prottype: Protect type, e.g. GRANT/REVOKE --
-- grantee : Grantee name. --

-- -- @flags added for DaVinci uses. If the bit isn't set, use 6.5 -- --
-- -- sp_MSobjectprivs '%s' -- --

-- 8.0: mode 'column', and grantee != null, we want user column level permissions for CTable/CView::ListUserColumnPermissions --
-- @rollup added to indicate special rollup result set for column level permission, set to 1 to roll up --

-- @flags is for daVinci --
if (@flags is null)
select @flags = 0

-- If @objid is not null, this is for the new query for perm cols. --
if (@objid is not null) begin
select schema_name(syso.schema_id), syso.name, a = col_name(p.id, a.number), a.low, a.high, a.number
from master.dbo.spt_values a, dbo.sysprotects p, sys.all_objects syso
where p.id = @objid and p.action = @srvpriv and p.protecttype = @prottype
and p.uid = user_id(@grantee)
and p.columns != 0x01 and syso.object_id = p.id
and convert(tinyint, substring(isnull(p.columns, 0x01), a.low, 1)) &
-- 6.5 changed so that the bit 0 position is an "invert the bits" indicator:
-- when 0, behaviour is the same as in prior versions, and other bits
-- indicate columns with the specified privilege
-- when 1, the other bits are indicate columns lacking the specified privilege
a.high <> (case when (substring(isnull(p.columns, 0x00), 1, 1) & 1 = 0) then 0 else a.high end)
and col_name(p.id, a.number) is not null
and a.type = N'P' and a.number <= (select count(*) from dbo.syscolumns where id = @objid) order by a
return 0
end

set nocount on

--
* To get around a 4.21 subquery bug where returning count(*) of 0 (for proc cols)
* causes the result set to return no rows, we need two passes; one to get the
* objects, and another to explicitly use a value (@cols) instead of a subquery.
--
declare @id int, @uid int, @cols int
select @id = null, @uid = null
if (@mode like N'us%') begin
select @uid = user_id(@objname)
end else if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
-- 8.0, special path to get column level permissions from all objects on the specified user --
select @uid = user_id(@grantee)
end else begin
select @id = object_id(@objname)
end
if (@id is null and @uid is null) begin
RAISERROR (15001, -1, -1, @objname)
return 1
end

-- Get a temp list of objects we're interested in. Do not include repl_* users. --
-- This is the original code --
insert #objs select distinct p.id from dbo.sysprotects p
where (@id is null or p.id = @id)
and (@uid is null or p.uid = @uid)
and p.action in (193, 195, 196, 197, 224, 26) and p.uid not in (16382, 16383)

-- Use a "fake cursor" by deleting successive id's from #objs, as this must run on 4.21 --
select @id = min(id) from #objs
while (@id is not null) begin
select @cols = count(*) from dbo.syscolumns c where c.id = @id
-- sysprotects.columns is for SELECT and UPDATE, NULL if it is INSERT or DELETE, since INSERT and DELETE can not be applied to column level --
insert #output select p.action, (case when p.columns is null then -1 else a.number end), p.uid, p.protecttype, p.id, p.grantor
from master.dbo.spt_values a, dbo.sysprotects p
where convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high !=0
and (p.id = @id)
and (@uid is null or p.uid = @uid)
and a.number <= @cols
and a.type = N'P'

declare @count int, @whataction int, @whatid int, @dup int, @whatprot int

-- First pass to correct duplicates --
select @count = count(*) from #output where id = @id and colid in (0, -1) and protecttype in (205, 204)
if ( @count > 0 ) begin
-- We might have duplicate rows for permission on single coulmn(s) at this point --
-- Use a fake cursor to remove the duplicates first. --
insert #tmp select action, uid, protecttype from #output where id = @id and colid in (0, -1) and protecttype in (205, 204)
select @whataction = min(action) from #tmp
select @whatid = uid from #tmp where action = @whataction
while (@whataction is not null) begin
if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
-- Special case for column level permissions on ALL objects for the specified user, we don't want the row(s) on the entire table --
-- and we don't want the possible duplicate rows in single column(s) --
delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction
and (exists (select * from #output where (@whatid = uid) and (colid in (0, -1)) and action = @whataction) and (id = @id))
delete #output where (@whatid = uid) and (colid in (0, -1)) and (action = @whataction) and (id = @id)
end else if (@mode like N'use%') and (@objname is not null) begin
-- Special case for the user mode, we do want to keep the entire table permissions --
delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction and (id = @id)
end else begin
-- Other cases --
delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction
end

delete #tmp where @whatid = uid
select @whataction = min(action) from #tmp
select @whatid = uid from #tmp where action = @whataction
end
delete #tmp
end

-- Second pass to correct protect type --
select @count = count(*) from #output where id = @id and colid in (0, -1)
if ( @count > 0 ) begin
-- use another fake cursor to correct the protecttype --
-- if there are multiple rows in #output for the same id and action, and if colid = 0 exist --
-- then other rows should have different protecttype from the one in colid = 0 row --
insert #tmp select action, uid, protecttype from #output where id = @id and colid in (0, -1)
select @whataction = min(action) from #tmp
select @whatid = uid from #tmp where action = @whataction
select @whatprot = protecttype from #tmp where uid = @whatid and action = @whataction
while (@whataction is not null) begin
delete #output where id = @id and colid not in (0, -1) and @whataction = action and @whatid = uid and @whatprot = protecttype
delete #tmp where action = @whataction and @whatid = uid
select @whataction = min(action) from #tmp
select @whatid = uid from #tmp where action = @whataction
select @whatprot = protecttype from #tmp where uid = @whatid and action = @whataction
end
delete #tmp
end

-- Increment our "fake cursor" column and get the next one. --
delete #objs where id = @id
select @id = min(id) from #objs
end

--
* Organize so that the non-collist privileges are returned first.. this allows
* scripting to combine them. sysprotects.action is tinyint, so the hibyte won't conflict.
--

update #output set action = action | 0x10000000 where colid <> 0

--
* BUG 235637
* Delete the columns that was droped
--
delete from #output where colid not in (0, -1) and col_name(id, colid) is null

--
* Order output by uid so Public will script before other groups (we need to script privs for public before
* other groups, before users; otherwise sysprotects doesn'
t hold onto things right). Sub-order is by object id
* so we know when we're done with one object and onto the next, then by protecttype to group all GRANTs and
* REVOKEs together, and lastly by action (including ORDER_ACTION_BIT so scripting can be more efficient)
* because we may have multiple rows for columns.
--

set nocount off
if (@mode not like N'
col%') begin
-- Mode is not '
column', do the regular stuff --
-- SQLDMO90OK - grantor can be only a user and the use of user_name is safe here --
select p.action & ~convert(int, 0x10000000), N'
column' = col_name(p.id, p.colid), p.uid, N'username' = user_name(p.uid),-- SQLDMO90OK --
p.protecttype, o.name, N'
owner' = schema_name(syso.schema_id), p.id, N'grantor' = user_name(p.grantor) -- SQLDMO90OK --
from #output p, dbo.sysobjects o, sys.all_objects syso
where o.id = p.id and o.id = syso.object_id
order by p.uid, p.id, p.protecttype, p.action
end else
-- Below are spcial cases for column level permissions --
if (@objname is null) and (@grantee is not null) and (@rollup = 0) begin
-- 8.0, special path to get column level permissions from all objects on the specified user --
select N'
ObjectName' = o.name, N'Owner' = schema_name(syso.schema_id), N'ColumnName' = col_name(p.id, p.colid), o.sysstat & 0x0f, p.id,
p.action & ~convert(int, 0x10000000), p.protecttype
from #output p, dbo.sysobjects o, sys.all_objects syso
where p.id = o.id and o.id = syso.object_id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
order by p.uid, p.id, p.protecttype, p.action
end else if (@objname is not null) and (@grantee is not null) and (@rollup = 0) begin
-- 8.0, mode '
column', and grantee != null, we want column level permissions on this object for this user --
select N'
column' = col_name(p.id, p.colid), N'owner' = schema_name(syso.schema_id), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id, -- SQLDMO90OK --
p.action & ~convert(int, 0x10000000), p.protecttype
from #output p, dbo.sysobjects o, sys.all_objects syso
where o.id = p.id and o.id = syso.object_id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
order by p.uid, p.id, p.protecttype, p.action
end else if (@objname is not null) and (@grantee is null) and (@rollup = 0) begin
-- 8.0, mode '
column', and grantee = null, we want column level permissions on this object for all users --
select N'
column' = col_name(p.id, p.colid), N'owner' = schema_name(syso.schema_id), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id, -- SQLDMO90OK --
p.action & ~convert(int, 0x10000000), p.protecttype
from #output p, dbo.sysobjects o, sys.all_objects syso
where o.id = p.id and o.id = syso.object_id and col_name(p.id, p.colid) is not null
order by p.uid, p.id, p.protecttype, p.action
end else if (@objname is null) and (@grantee is not null) and (@rollup <> 0) begin
-- 8.0, roll up version of the special path to get column level permissions from all objects on the specified user --
select distinct N'
ObjectName' = o.name, N'owner' = schema_name(syso.schema_id),
N'
Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
N'
Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
N'
Type' = p.protecttype
from #output p, dbo.sysobjects o, sys.all_objects syso
where p.id = o.id and o.id = syso.object_id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
order by o.name
end else if (@objname is not null) and (@grantee is null) and (@rollup <> 0) begin
-- 8.0, roll up version of the special path to return column level permissions on this object for all users --
select distinct N'
UserName' = user_name(p.uid), -- SQLDMO90OK --
N'
Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
N'
Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
N'
Type' = p.protecttype
from #output p, dbo.sysobjects o
where o.id = p.id and col_name(p.id, p.colid) is not null
order by user_name(p.uid)-- SQLDMO90OK --
end else begin
-- localize message without changing message number --
declare @errtxt nvarchar(1024)
select @errtxt=text from sys.messages where message_id=29003
RAISERROR(55555, 16, 1); -- @errtxt
return 1
end

No comments:

Post a Comment

Total Pageviews