I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

August Edition
Subscribe now! It's Free!
Enter your email:

   ► KBSQL ServersMicrosoft SQ...   Print This    All Groups  
  From the January 2016 Issue of Prestwood eMag
 
DBA Microsoft SQL Server:
Granting Execute Permissions for Stored Procedures
 
Posted 5 years ago on 6/10/2014
Take Away:

It's a well known best practice to use Stored Procedures instead of having logic in our application code.

It's also a best practice, when deploying an application, to have it access SQL Server by way of a relatively low-privileged user. After all, in most applications, that user needs only the CRUD operations.

But that'll come back to bite you when your application tries to use (execute) a stored procedure. Instead of the expected result, you'll get an error to the effect that the user doesn't have permission to execute stored procedures.

This is a quick, safe and solid way to get around that...

KB102563

It's a well known best practice to use Stored Procedures instead of having logic in our application code.

It's also a best practice, when deploying an application, to have it access SQL Server by way of a relatively low-privileged user. After all, in most applications, that user needs only the CRUD operations.

But that'll come back to bite you when your application tries to use (execute) a stored procedure. Instead of the expected result, you'll get an error to the effect that the user doesn't have permission to execute stored procedures.

This is a quick, safe and solid way to get around that.

First, credit where credit is due: Thanks to my colleague, Jim Lucan, for sharing the core of this idea.  Second, thanks to Kennith Hampton for sharing the code, below, on StackOverflow, here:

http://stackoverflow.com/questions/18973285/how-to-grant-execute-permissions-to-the-stored-procedures-in-a-specific-schema

The idea is that we need to grant the EXECUTE permission to our lower-permission user, and do so for each stored procedure in our solution.

This can be done manually, in Management Studio and, although not difficult, it would be tedious if your database has many stored procedures.

Kenneth Hampton's code takes care of all of this almost instantly:

DECLARE @SchemaName varchar(20)
DECLARE @UserName varchar(20)

SET @SchemaName = 'dbo'
SET @UserName = 'BenArchUser'

select 'GRANT EXECUTE ON OBJECT::' + @SchemaName + '.' + P.name  + ' to ' + @UserName
from sys.procedures P
inner join sys.schemas S on P.schema_id = S.schema_id
where S.name = @SchemaName

This is one of those invaluable bits of code that goes into my personal "snippets" collection!


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1251A1
Enter key:
Article Contributed By Wes Peterson:

Wes Peterson is a Senior Programmer Analyst with Prestwood IT Solutions where he develops custom Windows software and custom websites using .Net and Delphi. When Wes is not coding for clients, he participates in this online community. Prior to his 10-year love-affair with Delphi, he worked with several other tools and databases. Currently he specializes in VS.Net using C# and VB.Net. To Wes, the .NET revolution is as exciting as the birth of Delphi.

Visit Profile

 KB Article #102563 Counter
5037
Since 6/10/2014
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech