What permissions does the MS SQL user need on the database to execute (and read the data of) a stored procedure?

What permissions does the MS SQL user need on the database to execute a stored procedure? By execute it I mean they can run it and read the data. I don't want them to have permissions to any other tables or areas of the DB.

Do they need permissions to the tables that the Stored Procedure queries?

Do they need permissions to the database which contains the stored procedures?
intoxicated_curveballAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Do they need permissions to the database which contains the stored procedures?
Yes.

Do they need permissions to the tables that the Stored Procedure queries?
Yes.

What permissions does the MS SQL user need on the database to execute a stored procedure?  By execute it I mean they can run it and read the data.
GRANT EXECUTE ON sp_name TO user_name
GRANT SELECT ON table_name TO user_name

Open in new window

Repeat the above for each object and user that you want to provide the permission.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
intoxicated_curveballAuthor Commented:
Thanks. What are the minimum permissions that they need to the database in order for the above permissions to work?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You should be save with db_denydatawriter - that prevents them from changing anything. Read access requires additional privileges on object level (table, view etc.) as shown above.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Scott PletcherSenior DBACommented:
Do they need permissions to the database which contains the stored procedures?
Perhaps.  But just CONNECT permission is all.

Do they need permissions to the tables that the Stored Procedure queries?
It depends.  You can specify a different user to run the proc under (EXEC AS ...).  If you do that, the caller will not need permissions to the any objects used by the proc, because they won't be running it anyway.  This is a very common way to avoid having to give users permissions other than EXEC PROC proc_name.  That's what I'd recommend you do for such situations.

Even if not, once you GRANT EXEC on the proc to the user, if the objects within the proc are in the same schema as the proc itself, often the caller doesn't need separate permission to those.  "Ownership Chaining" takes care of it.

One exception is dynamic SQL: it always requires direct permission to the objects it references.

Another possibility, altho a risky one, would be to grant a "guest"/anyone as permission to read (only) a certain table.  But then of course they could read it outside of that proc.
0
Russell FoxDatabase DeveloperCommented:
I generally create a db_executor roll to make my life easier: https://www.sqlmatters.com/Articles/Adding%20a%20db_executor%20role.aspx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
With no more feedback from the author, this is my recommendation to close this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.