Thread: User Rights Restriction
07-16-08, 02:51 #1Registered User
- Join Date
- Sep 2007
Unanswered: User Rights Restriction
One Web Application is using SQL server via user 'abc'. I want to restrict this user.
My requirement is :
abc should not select, insert, update, delete on any table using any sql query.
abc should run specific Stored Procedure and insert or delete or update or select.
I have procedures for all this operations.
What steps I have to follow to create a login, role, user?
what rights i have to give to database, table?
please guide in detail.
07-16-08, 08:54 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
Use schemas for all your objects, create schema for your tables and deny select,insert,update,delete on those schemas to that user. Create a schema or schemas for your stored procedures and grant execute on it/them to that user. When creating each stored procedure, use EXECUTE AS clause and either specify a user account that has the rights to perform DML on table schemas, or use OWNER."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."