Sql-server – How to give access to a user on only two specified procedures and specified tables on SQL Server

sql serverstored-procedures

We would like to introduce an external orchestrator in order to feed some tables from two stored procedures on SQL Server. However, we will need to create an 'orchstr' user for the orchestrator.
We want to give access to the user 'orchstr' to execute only the two specified stored procedures (so there is a restriction on the other procedures). And access on the tables to be fed only.

Is it possible? How could we do this?

I'm still a beginner, it would be too kind of you to help me.

We have SQL Server 2012 Enterprise Edition.

Best Answer

Bu default, a user don't have any premissions. So, all you have to do is to grant the permissions you want:

GRANT EXEC ON proname TO usrname
GRANT SELECT ON tablename TO username

I don't understand what you mean by "tables to be fed".

If a proc operates on a table (SELECT, INSERT ...) then it is enough to have EXEC permissions on the proc, you don't need the (SELECT, INSERT...) privilege on the table in order to execute the proc. This assumes the same over of the proc as the table, called "ownership chaining".

Related Question