Experience Sharing, SQL Databases

[SQL] Grant User Permissions


The task which I was doing today required me to prepare a SQL script which grants the user to execute the user defined functions and stored procedures. While SQL Server Management Studio allows us to set the permission by a few clicks, it is good to prepare them using the script so we can re-use them in case we have run them in the development and production databases.

A simple script to grant user with execute permission for stored procedures. It applies to all stored procedures in the selected database.

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO <user>

Next, I found out that my application needs to set permission for the user defined functions. I felt I could have spent the same amount of time to search the script to automate the process and manually wrote the script one by one. The script below can help,

/* GRANT EXECUTE TO THE USER DEFINED FUNCTIONS */
SELECT 'GRANT EXECUTE ON [' + SCHEMA_NAME(schema_id) + '].[' + [name] + '] TO MyRole;'
FROM sys.objects
WHERE type IN ('TF','FN');

There is another script which could help,

declare @name varchar(100), @qry varchar(2000)
declare cursor cursor_temp
for select name from dbo.sysobjects where OBJECTPROPERTY (id, IsProcedure) = 1
open cursor_temp
fetch next from cursor_temp into @name
while @@fetch_status = 0
begin
set @qry = ' grant execute on [dbo].[' + @name + ' ] to [ <user_name> ]'
exec (@qry)
fetch next from cursor_temp into @name
end
close cursor_temp
deallocate cursor_temp

I hope this could help.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s