January 19, 2005 at 3:43 pm
I am trying to script the permissions of a specific user / role. The information i am looking for is User/Role ID, Permission Type (Grant, Deny, Etc), Grantor, Table, and Column if column level permission. I know I can use sp_helprotect to get a lot of this information, but I am having problems using it to generate my reports and dynamic sql statement to recreate the permissions. The problem i am having is linking the sysprotects.columns (varbinary) to syscolumns table.
Grantee / Grantor / Table / Column / Type
Public DBO Orders * Update
Public DBO Orders Freight Update
Public DBO Customers * Select
January 20, 2005 at 4:51 am
Have you consider using views since you are preparing for reports (i.e., not updating or deleting data)
Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced the same way as tables in SQL statements (BOL)
January 20, 2005 at 8:10 am
I understand i can use views to make reporting easier. The problem i have is that in the sysprotects table the columns field is varbinary. I am having trouble finding the specific column when it's stored as varbinary. I will give an example. I granted myself update permissions to Northwind.dbo.orders.freight column. When i query sysprotects for permissions related to my UID it returns
ID (SYSObjects) UID (SysUsers) Action ProtectType Columns
21575115 5 197 205 0x1A01
I understand what ID, UID, Action, and ProtectType mean. What I can't do is obtain the column name from the varbinary data in the "Columns" column. How do i use varbinary data to obtain the column name or use it to make a join to sysobjects or syscolumns. I appreciate any help on this.
January 20, 2005 at 5:23 pm
This will do a lot more than what you have asked, so you can cut it down but this is what I use to learn the maximum about a table. This procedure is written to examine every table in the database. This in NOT my work but something copied from this forum, posted by a user whose name I have forgotten. So to the unknown person should go all the credit.
CREATE PROCEDURE UDP_Table_Definitions
AS
Declare @default VARCHAR(128)
Declare @tname VARCHAR(128)
Declare @cname VARCHAR(128)
Declare @dtype VARCHAR(30)
Declare @dlength INT
Declare @sstatus CHAR(3)
Declare @xcomp CHAR(3)
Declare @xdef INT
SET NOCOUNT ON
Create Table #UDT_TDefs
(
TName VARCHAR(128),
CName VARCHAR(128),
DType VARCHAR(30),
Dlength INT,
Sstatus CHAR(3),
xComp CHAR(3),
xDef VarChar(30)
)
DECLARE table_cursor CURSOR fast_forward FOR
SELECT so.name, sc.name, st.name, sc.length, sc.Status, sc.iscomputed, sc.cdefault
From Sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U' and sc.id = so.id and sc.xtype = st.xusertype AND NOT so.name = 'dtproperties'
Open Table_Cursor
Fetch Next from Table_Cursor into @tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @xdef
While @@Fetch_Status = 0
Begin
Set @Default = ' '
If @xdef > 0
Set @Default = (Select text from syscomments where @xdef = id)
Insert Into #UDT_TDefs
(TName, CName, DType, Dlength, Sstatus, xComp, xDef)
Values (@tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @Default )
Fetch Next from Table_Cursor into @tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @xdef
End
Close Table_Cursor
Deallocate Table_Cursor
Select
tname As 'Table Name', cname as 'Column Name', DType as 'Data Type', dlength as 'Len', sStatus as 'NP1', xComp as 'NP2', xdef as 'Default',
Case tname When 'int' then Cast(dlength as Char(6)) Else ' ' end As 'Precision',
Case sStatus When 8 then 'Yes' When 24 then 'Yes' When 56 then 'Yes' Else ' ' end As 'Allow Nulls',
Case sStatus When 128 then 'Yes' Else ' ' end As 'Identity',
Case xcomp When 1 then 'Yes' Else ' ' end As 'Computed'
From #UDT_TDefs
Order by tname
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply