August 11, 2009

Finding permission discrepancies on SQL Server

After almost an hour of bashing my head against some code trying to fix a problem with an application today that I'd tested and deemed working I finally figured out why someone else who was trying to replicate my tests was having so many problems. It turns out that I'd installed two SQL scripts on the database, but had inadvertently forgotten to assign the user group permissions. Having grown tired of trying to figure out which scripts haven't been assigned the execute permission and which have, and figuring out which scripts have been added recently and have been potentially overlooked, I decided that it would be a great idea if I just created a script that assigned execute permissions to all of the stored procedures in my database.

Before you run this code, there are some caveats to be aware of:

  1. It will assign execute permission on ALL of your custom stored procedures to the user group you specify in the @userAcct variable
  2. You should not use this as part of a stored procedure - the SQL is dynamic and not parameterized. If it is included in a stored procedure and you are subject to a SQL injection attack, it is not my fault and I shall not be held responsible - you have been warned
/* Set the user name of the security account you wish to grant permissions to */
Declare @userAcct NVarChar(255)
Set @userAcct = '[MYDOMAIN\Sql Users]'

Declare CRS_STORED_PROCS Cursor For
 Select name
 From SysObjects
 Where xtype = 'P'
 And category = 0

Declare @procName NVarChar(255)
Open CRS_STORED_PROCS
Fetch Next From CRS_STORED_PROCS Into @procName
While (@@Fetch_Status = 0)
 Begin
  Print 'Granting execute permissions on procedure '+ @procName + ' to account ' + @userAcct + '...'

  Declare @stmt NVarChar(255)
  set @stmt = 'Grant Execute On ' + @procName + ' To ' + @userAcct

  execute sp_executesql @stmt

  Print 'Permissions granted.'

  Fetch Next From CRS_STORED_PROCS Into @procName
 End

Close CRS_STORED_PROCS
Deallocate CRS_STORED_PROCS

Some other useful queries follow

Give me a list of all stored procedures that I created on or since a specified date:

Select name 
From  SysObjects 
Where  xtype = 'P' 
And category = 0 
And crdate > '2009-08-10' 
Order By crdate Desc

Give me a list of all stored procedures that my user has permission to execute.

Select c.name As [Procedure],
 Case a.actadd
  When 32 Then 'True'
  When 0 Then 'False'
 End As [Execute],
 Case a.actmod
  When 32 Then 'True'
  When 0 Then 'False'
 End As [With Grant]
From SysPermissions a
 Left Join SysUsers b On a.Grantee = b.uid
 Left Join SysObjects c On a.id = c.id
Where b.name = 'MYDOMAIN\Sql Users'
And c.Type = 'P'

Give me a list of all stored procedures that my user does not have permissions for.

Select a.name as [Procedure Name],
 a.crdate as [Created]
From SysObjects a
Where type = 'P'
And category = 0
And id Not In (
 Select id 
 From SysPermissions a 
  Left Join SysUsers b On a.Grantee = b.uid
 Where b.name = 'MYDOMAIN\Sql Users' 
)

Get a list of tables that have changed structurally since a known specified date:

Declare @LastKnownChangeDate DateTime

Select @LastKnownChangeDate = '2009-08-01'

select name As TableName, 
  create_date As Created,
  modify_date As LastModified
From sys.tables
Where type_desc = 'USER_TABLE'
And  modify_date > @LastKnownChangeDate

The permissions found for users of stored procedures and tables are found in the SysPermissions tables. The columns actadd, actmod, seladd, selmod, updadd, updmod, refadd and refmod refer to the permissions assigned to the user specified in the grantee column, the grantee and grantor columns reference the uid field in the SysUsers table. The numbers found in the permissions columns are a bitmask for the permissions assigned such that:

  • Select = 1
  • Update = 2
  • References = 4
  • Insert = 8
  • Delete = 16
  • Execute = 32

For those of you not familiar with bitmasks a quick example is that it's like a bunch of flags either being on or off. Each flag represents the number specified and the sum of the enabled flags is the value stored. For instance, If a user has select, insert, update and delete permissions, the value would be 1 + 2 + 8 + 16 = 27. If the user only has execute permission on a stored procedure, then the value would be 32.

No comments:

Post a Comment