Menu
Is it possible to deny permissions for everything in SQL Server (e.g select, insert, etc) and give temporary permissions through stored procedures. I want to use only stored procedures to access data in sql database.
Is this possible or is there another way?
592
Right click on your procedure and select Properties. You'll get the following window. As shown inthe preceding image, go to Permissions tab and click on Search button. On click you'll get a window to select user and roles, click on Browse to select users that require permission and click OK.
To grant permissions to a user, database role, or application role, select Search. In Select Users or Roles, select Object Types to add or clear the users and roles you want. Select Browse to display the list of users or roles. Select the users or roles to whom permissions should be granted.
Stored procedures promote bad development practices, in particular they require you to violate DRY (Don't Repeat Yourself), since you have to type out the list of fields in your database table half a dozen times or more at least. This is a massive pain if you need to add a single column to your database table.
A stored procedure is a set of SQL statements with an assigned name, which can be stored for later use so it can be reused and shared by multiple programs. Stored procedures also allow admins to apply permissions to users.
This is a good pattern supported my SQL Server.
Basically, whoever has permissions on the the stored procedures does not need any table permissions at all, if the proc and tables have the same owner (dbo usually). It's called ownership chaining
Note: permissions on the tables are simply not checked in this situation, so explicit "DENY permissions" will be ignored too (DENY is different to "no permissions")
The same applies to views, functions etc (eg view calling table, proc calling view, ...)
156
Yes this is called ownership chaining, if you give execute permission to the proc the user will be able to execute the proc and it will work, he will get data back
if he then tries to do a select from the table from SSMS it will not work (providing that he is not in the data reader or higher role)
Keep in mind that dynamic SQL breaks ownership chaining, if you have dynamic SQL in the stored procedure, you will need extra permissions in that case
31
This does not only work with stored procedures, but also with views.
In general, you can give a user permission on a "higher-level" object like a view or sproc, without having to give the same user permission on the underlying table(s) as well.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
