Grant Execute Permission to all Stored Procedures and Functions

How to grant execute permission to all your stored procedures and functions

Another useful stored procedure I have in my toolkit that I find myself using over and over on any system that I work on is the following proc that allows me to grant execute permission to all stored procedures and user defined functions within a database to a particular logon.

Sometimes I find myself creating multiple stored procedures or functions in one go, either through a CRUD proc generator script or just manually but without the necessary execute permissions added to each procedure or function they will fail to execute. Therefore its very handy if you have forgotten to add the GRANT EXECUTE statements as you went along to do it all in one batch.

The procedure makes use of the sysobjects system view to return a list of all the stored procedures and user defined functions within the database the procedure is called from. I then loop through each one and generate the necessary SQL to apply EXECUTE permission to that proc or UDF to the supplied logon.

I have also added a flag which allows you to print all the GRANT EXECUTE statements without running them so that you can check the output before running it.

The code is below.

CREATE PROCEDURE [dbo].[usp_sql_grant_execute_permission] 

 @login VARCHAR(50) = 'mylogon',
 @RunEXEC BIT = 1 --1 will run the statements, 0 = Prints them

AS

SET NOCOUNT ON

DECLARE @procname VARCHAR(50),
  @Rows INT,
  @Count INT

-- Populate the temporary table with user defined stored procs and functions
SELECT [name] INTO #tmp 
FROM sysobjects 
WHERE (xtype = 'P') OR  (xtype = 'FN' OR xtype = 'TF')

SELECT @Rows = @@ROWCOUNT, @Count = 0


-- Iterate through each of the rows on the table
WHILE (@Count < @Rows)
BEGIN
 -- get next item
 SELECT TOP 1 @procname = [name] FROM #tmp

 -- Prepare and execute SQL statement
 DECLARE @sql VARCHAR(255)
 SET @sql = 'GRANT EXEC ON ' + @procname + ' TO ' + @login
 
 -- do we run or print them out
 IF @RunEXEC = 1
   EXEC (@sql)
 ELSE
   PRINT @SQL
 
 -- delete record from temp table 
 DELETE #tmp WHERE [name] = @procname
 
 SELECT @Count = @Count + 1
END

You call it simply like so:

EXEC usp_sql_grant_execute_permission 'mylogin'

It's one of those simple procedures that are very handy to have and save a lot of time. Its very quick to run so if I ever come across any permission denied errors on my development system I just run this procedure first to ensure all my procedures and functions have the necessary execute permissions to run correctly.

The database owner SID recorded in the master database differs from the database owner SID recorded in database

Problems with database ownership SID values

I recently came across a problem which I have had a couple of times now which seems to be related to database backups in SQL Server 2005.

I was carrying out a manual backup of a database on the production server which was taking a very long time. During this process I came across another problem which needed solving quickly and I couldn't wait until the backup had finished before trying to resolve the issue which was caused by a report called from one database trying to access data from another. It was raising the following error message:

The server principal "XXXXX" is not able to access the database "XXXX" under the current security context.

The solution to this problem is to enable the database that needs to be accessed as Trustworthy with an SQL statement like this:

ALTER DATABASE strategies_jobboard SET TRUSTWORTHY ON

So I ran this statement to fix the reporting issue and I had some hesitations about doing this as I didn't know whether the combination of the backup and the lock on the Database would cause any issues and low and behold just after running this statement I started receiving the following errors:

The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXX'. You should correct this situation by resetting the owner of database 'XXXX' using the ALTER AUTHORIZATION statement.

Now this is an error I have come across before when a database has been created on one server by restoring a backup taken from another server and the database owners differ. To prove that the problem is in fact differing SID's I ran the following two SQL statements.

--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

They should return you SID values in the format of a GUID e.g

0x010500000000000515000000BF76FC16EA335FE078A67D48FB030000

Now if the two SID's differ which they did in my case it means that you need to reset the database owner so that both values are the same. To do this you can run another ALTER statement and pass in the owner value you want to use e.g

ALTER AUTHORIZATION ON Database::XXXX TO [domain\user]

Once I had run this code the problem was fixed. Now I am pretty sure the issue was down to a combination of the backup running at the same time as the code to change the TRUSTWORTHY statement as I had no errors before that SQL was run and as soon as I had executed it the errors started shooting into my inbox.

However I tried to replicate this problem on a development server by running some backups and executing the same SQL but I couldn't re-create the problem. Maybe there was something else in the mix I was unaware of but I thought I would post the solution to the problem anyway in case others have a similar situation arise.