0

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.

1

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".

2
  • i meant that we have to insert some data on the specified tables with stored procedures – Lucas Rasolofoniaina Apr 29 at 10:32
  • So the proc will do the insert, and all the user will do is to call the proc? Then exec permissions on the proc will do. Assuming the same owner of the proc as on the tables. – Tibor Karaszi Apr 29 at 11:49
1

Yes, it is possible. First you need to add that login as a user in that database without any database role assigned except public role(no server role as well). Once login is added as a user in the database, you need to execute explicit grant permission as below:

use db_name
go
grant execute on dbo.Proc_Name1 to User_name
go
grant execute on dbo.Proc_Name2 to User_name
go
grant select on dbo.Table_name to User_name
go

In above example, I am considering you are having default schema(dbo). You may read more here.

1
  • 1
    Thank you . To sum up, I just have to create the user and give permissions on the stored procedures and tables with that given script to the user – Lucas Rasolofoniaina Apr 29 at 10:35
0

If you have multiple objects inside your stored procedures. Then you can try with execute as owner option in your stored procedure. No need to give individual permissions for objects used in stored procedures.

New contributor
Viraj Sawant is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.