SQL Server update/insert/delete table records only from stored procedures

Question

Is there any way to be sure that a table will be modified (INSERT/DELETE/UPDATE) only by stored procedures in SQL Server without touching the permissions?

If you want a table only to be modified by a sproc you need to apply permissions to the table. In addition, deny any objects / any users / except for the user that needs to run the sproc as well as the sprocs. — JonH
– JonH, Commented Aug 6, 2012 at 14:29
You don't need to grant table permissions to the user running the SP. They will have permissions by nature of running the stored procedure — podiluska
– podiluska, Commented Aug 6, 2012 at 14:36

podiluska · Accepted Answer · 2012-08-06 14:29:40Z

2

No. That's what permissions are for.

answered Aug 6, 2012 at 14:29

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Losbear · Accepted Answer · 2012-08-06 14:35:03Z

1

Unfortunately i don't think there is a way to do it without touching permissions. The only way I know to do this is to

setup a user that can only call SP's (no access to any tables).
in each SP, you'll have to impersonate a user that has full access to the tables using "EXECUTE AS" (http://msdn.microsoft.com/en-us/library/ms188354.aspx)
Applications then use the new user you created in step one to call SP's.

answered Aug 6, 2012 at 14:35

Losbear

3,3231 gold badge34 silver badges29 bronze badges

1 Comment

podiluska Over a year ago

You don't have to use execute as.

Collectives™ on Stack Overflow

SQL Server update/insert/delete table records only from stored procedures

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related