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:
- It will assign execute permission on ALL of your custom stored procedures to the user group you specify in the @userAcct variable
- 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.