Documenting users permissions
-
I'm trying to get a handle on our user security, who has access to what, etc. But with 15 servers and way too many databases - various users, roles, etc. I'm not coming up with an easy way to track this down.
Has anyone had any success with scripting a way to extract security on database objects?
I can go trhrough EM and have it generate a script for me, but I'd like to figure out someway to automate that on a regular basis so I always have an up to date script of security for recovery purposes, but also for auditing.
I'd really like to expand that to scripting the databases, and SQL configuration, but I'd settle for being able to automate the scripting of my security settings for now... if this has been talked to death in the past, just point me in the right direction and I'll be happy to do my reading.
Thanks for your help.
Mary Elizabeth
-
This was removed by the editor as SPAM
-
Check out sp_helprotect in BOL. I use this within a dts to pull all permissions on a nightly basis into a table along with the current date. I have another that will allow me to read these permissions back from the table and reassign them for every user on a given date.
It's also useful when someone comes to me and says that they used to have permissions on an object last week and now don't as I am able to confirm this or tell them to go back to their cubicle (in a nice way of course).
Let me know if you need a little more help getting along with this.
-
This is exactly the sort of thing I'm trying to do - thank you! I will check out sp_helprotect.
-
I hacked the sp_Protect stored procedure to produce the following which will return a table in a similar manor to the permissions view in Enterprise Manager.
NB. It currently only returns objects where permissions have been set.
CREATE PROCEDURE dbo.sp_object_permissions
@name ncharacter varying(776) = NULL
,@username sysname = NULL
,@grantorname sysname = NULL
,@permissionarea character varying(10) = 'o s'
as
/********
Explanation of the parms...
---------------------------
@name: Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
Examples- 'user2.tb' , 'CREATE TABLE', null
@username: Name of the grantee (for sysprotects.uid).
Examples- 'user2', null
@grantorname: Name of the grantor (for sysprotects.grantor).
Examples- 'user2' --Would prevent report rows which would
-- have 'dbo' as grantor.
@permissionarea: O=Object, S=Statement; include all which apply.
Examples- 'o' , ',s' , 'os' , 'so' , 's o' , 's,o'
GeneMi
********/
Set nocount on
Declare
@vc1 sysname
,@Int1 integer
Declare
@intMaxLenOwner int
,@intMaxLenObject int
,@intMaxLenGrantee int
,@intMaxLenGrantor int
,@intMaxLenAction int
,@intMaxLenColumnName int
Declare
@OwnerName sysname
,@ObjectStatementName sysname
/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
( Id int Null
,Type1Code char(6) collate database_default NOT Null
,ObjType char(2) collate database_default Null
,ActionName varchar(20) collate database_default Null
,ActionCategory char(2) collate database_default Null
,ProtectTypeName char(10) collate database_default Null
,Columns_Orig varbinary(32) Null
,OwnerName sysname collate database_default NOT Null
,ObjectName sysname collate database_default NOT Null
,GranteeName sysname collate database_default NOT Null
,GrantorName sysname collate database_default NOT Null
,ColumnName sysname collate database_default Null
,ColId smallint Null
,Max_ColId smallint Null
,All_Col_Bits_On tinyint Null
,new_Bit_On tinyint Null ) -- 1=yes on
/* Check for valid @permissionarea */
Select @permissionarea = upper( isnull(@permissionarea,'?') )
IF ( charindex('O',@permissionarea) <= 0
AND charindex('S',@permissionarea) <= 0)
begin
raiserror(15300,-1,-1 ,@permissionarea,'o,s')
return (1)
end
select @vc1 = parsename(@name,3)
/* Verified db qualifier is current db*/
IF (@vc1 is not null and @vc1 <> db_name())
begin
raiserror(15302,-1,-1) --Do not qualify with DB name.
return (1)
end
/* Derive OwnerName and @ObjectStatementName*/
select @OwnerName = parsename(@name, 2)
,@ObjectStatementName = parsename(@name, 1)
IF (@ObjectStatementName is NULL and @name is not null)
begin
raiserror(15253,-1,-1,@name)
return (1)
end
/* Copy info from sysprotects for processing */
IF charindex('O',@permissionarea) > 0
begin
/* Copy info for objects */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On 
/* 1Regul indicates action can be at column level,
2Simpl indicates action is at the object level */
SELECT id
,case
when columns is null then '2Simpl'
else '1Regul'
end
,Null
,val1.name
,'Ob'
,val2.name
,columns
,user_name(objectproperty( id, 'ownerid' ))
,object_name(id)
,user_name(uid)
,user_name(grantor)
,case
when columns is null then '.'
else Null
end
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@OwnerName is null or user_name(objectproperty( id, 'ownerid' )) = @OwnerName)
and (@ObjectStatementName is null or object_name(id) = @ObjectStatementName)
and (@username is null or user_name(uid) = @username)
and (@grantorname is null or user_name(grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and val2.type = 'T' --T is overloaded.
and val2.number = sysp.protecttype
and sysp.id != 0
IF EXISTS (SELECT * From #t1_Prots)
begin
UPDATE #t1_Prots set ObjType = ob.xtype
FROM sysobjects ob
WHERE ob.id = #t1_Prots.Id
UPDATE #t1_Prots
set Max_ColId = (select max(colid) from syscolumns sysc
where #t1_Prots.Id = sysc.id) -- colid may not consecutive if column dropped
where Type1Code = '1Regul'
/* First bit set indicates actions pretains to new columns. (i.e. table-level permission)
Set new_Bit_On accordinglly */
UPDATE #t1_Prots SET new_Bit_On =
CASE convert(int,substring(Columns_Orig,1,1)) & 1
WHEN 1 then 1
ELSE 0
END
WHERE ObjType <> 'V' and Type1Code = '1Regul'
/* Views don't get new columns */
UPDATE #t1_Prots set new_Bit_On = 0
WHERE ObjType = 'V'
/* Indicate enties where column level action pretains to all
columns in table All_Col_Bits_On = 1 */
UPDATE #t1_Prots set All_Col_Bits_On = 1
where #t1_Prots.Type1Code = '1Regul'
and not exists
(select *
from syscolumns sysc, master..spt_values v
where #t1_Prots.Id = sysc.id and sysc.colid = v.number
and v.number <= Max_ColId -- column may be dropped/added after Max_ColId snap-shot
and v.type = 'P' and
/* Columns_Orig where first byte is 1 means off means on and on mean off
where first byte is 0 means off means off and on mean on */
case convert(int,substring(#t1_Prots.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
else (~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
end & v.high = 0)
/* Indicate entries where column level action pretains to
only some of columns in table All_Col_Bits_On = 0*/
UPDATE #t1_Prots set All_Col_Bits_On = 0
WHERE #t1_Prots.Type1Code = '1Regul'
and All_Col_Bits_On is null
Update #t1_Prots
set ColumnName =
case
when All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
when All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
when All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
end
from #t1_Prots
where ObjType IN ('S ' ,'U ', 'V ')
and Type1Code = '1Regul'
and NOT (All_Col_Bits_On = 0 and new_Bit_On = 0)
/* Expand and Insert individual column permission rows */
INSERT into #t1_Prots
(Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId 
SELECT prot1.Id
,'1Regul'
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,col_name ( prot1.Id ,val1.number )
,val1.number
from #t1_Prots prot1
,master.dbo.spt_values val1
,syscolumns sysc
where prot1.ObjType IN ('S ' ,'U ' ,'V ')
and prot1.All_Col_Bits_On = 0
and prot1.Id = sysc.id
and val1.type = 'P'
and val1.number = sysc.colid
and
case convert(int,substring(prot1.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
else (~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
end & val1.high <> 0
delete from #t1_Prots
where ObjType IN ('S ' ,'U ' ,'V ')
and All_Col_Bits_On = 0
and new_Bit_On = 0
end
end
/* Handle statement permissions here*/
IF (charindex('S',@permissionarea) > 0)
begin
/* All statement permissions are 2Simpl */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On 
SELECT id
,'2Simpl'
,Null
,val1.name
,'St'
,val2.name
,columns
,'.'
,'.'
,user_name(sysp.uid)
,user_name(sysp.grantor)
,'.'
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@username is null or user_name(sysp.uid) = @username)
and (@grantorname is null or user_name(sysp.grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and (@ObjectStatementName is null or val1.name = @ObjectStatementName)
and val2.number = sysp.protecttype
and val2.type = 'T'
and sysp.id = 0
end
IF NOT EXISTS (SELECT * From #t1_Prots)
begin
raiserror(15330,-1,-1)
return (1)
end
/* Calculate dynamic display col widths */
SELECT
@intMaxLenOwner = max(datalength(OwnerName))
,@intMaxLenObject = max(datalength(ObjectName))
,@intMaxLenGrantee = max(datalength(GranteeName))
,@intMaxLenGrantor = max(datalength(GrantorName))
,@intMaxLenAction = max(datalength(ActionName))
,@intMaxLenColumnName = max(datalength(ColumnName))
from #t1_Prots
/* Output the report */
SELECT Owner = substring (OwnerName ,1 ,@intMaxLenOwner),
Object = substring (ObjectName ,1 ,@intMaxLenObject),
Grantee = substring (GranteeName ,1 ,@intMaxLenGrantee),
Grantor = substring (GrantorName ,1 ,@intMaxLenGrantor),
ProtectType = ProtectTypeName,
[Action] = substring (ActionName ,1 ,@intMaxLenAction),
[Column] = substring (ColumnName ,1 ,@intMaxLenColumnName)
into #temp
from #t1_Prots
order by
ActionCategory
,Owner ,Object
,Grantee ,Grantor
,ProtectType ,Action
,ColId --Multiple -123s ( <0 ) possible
SELECT
GRANTEE,
OWNER+'.'+OBJECT,
SUM(CASE WHEN [ACTION]='SELECT' AND PROTECTTYPE='Grant' THEN 1 WHEN [ACTION]='SELECT' AND PROTECTTYPE='Deny' THEN 2 ELSE 0 END) AS [SELECT],
SUM(CASE WHEN [ACTION]='INSERT' AND PROTECTTYPE='Grant' THEN 1 WHEN [ACTION]='INSERT' AND PROTECTTYPE='Deny' THEN 2 ELSE 0 END) AS [INSERT],
SUM(CASE WHEN [ACTION]='UPDATE' AND PROTECTTYPE='Grant' THEN 1 WHEN [ACTION]='UPDATE' AND PROTECTTYPE='Deny' THEN 2 ELSE 0 END) AS [UPDATE],
SUM(CASE WHEN [ACTION]='DELETE' AND PROTECTTYPE='Grant' THEN 1 WHEN [ACTION]='DELETE' AND PROTECTTYPE='Deny' THEN 2 ELSE 0 END) AS [DELETE],
SUM(CASE WHEN [ACTION]='EXECUTE' AND PROTECTTYPE='Grant' THEN 1 WHEN [ACTION]='EXECUTE' AND PROTECTTYPE='Deny' THEN 1 ELSE 0 END) AS [EXECUTE]
FROM #TEMP
GROUP BY GRANTEE, OWNER+'.'+OBJECT
ORDER BY GRANTEE, OWNER+'.'+OBJECT
Return (0) -- sp_helprotect
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply