June 9, 2011 at 10:41 am
We have a team developing a new application. The application's data resides in a SQL 2008 database. I want to allow the developers to execute stored procedures (create, update, delete) and to retrieve data only from views.
How can I alter an existing user's permission levels to ensure that they can no longer see the underlying data tables directly?
June 9, 2011 at 10:49 am
You have to first determine what permissions are currently granted to the developers.
Are all the developer logins in any roles?
You can explicitly deny permissions on objects which should override any grants given or you can just choose to remove the grant permission.
You can then grant them execute on the proper stored procedure and select on views.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
June 9, 2011 at 12:54 pm
Short way: grant new permissions first, then revoke old ones.
As mentioned above, you need to determine their permissions. You can use this to help: http://msdn.microsoft.com/en-us/library/ms176097.aspx
What you want to do is set up a role, the grant that role permissions to execute stored procs and then views if needed. Once that's done, move developers into that role.
Lastly, remove other permissions (REVOKE), not DENY.
June 9, 2011 at 1:44 pm
Many thanks for your advice folks!
Steve: Your response just begs this question: What is the difference between DENY and REVOKE then?
June 9, 2011 at 1:59 pm
By default, you have no permissions on an object, say a table called "Sales"
If I use GRANT, I add permissions for you to view sales.
If I then use REVOKE, I remove those permissions, so it's back to the default.
If I use DENY, you are prevented from seeing the table, even if someone subsequently grants you SELECT, say through a role.
DENYs end up hiding permissions sometimes and it can be a pain to track down the issues.
If you DENY the developers their current permissions after GRANTing new ones in a role, they won't be able to see the data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply