June 13, 2017 at 12:26 am
Account using the application to connect to DB have datareader,datawriter and execute permissions. Application deletes data from sometable. How can I restrict the user to delete data only from these tables but the same time need to insert it into a history table and should not allow delete from other tables Or will keep the user permission as it is and a new table will be used to insert all deleted data but the table structure wont be a problem in that case?
June 13, 2017 at 4:38 am
Two options:
(1) Do all DML with stored procedures. Then you don't need to provide permissions on tables or db_datareader/writer access - only EXECUTE on the stored procedures.
(2) Get rid of the db_datareader/writer membership and grant DELETE or INSERT permission on the individual tables as appropriate.
John
June 13, 2017 at 4:50 am
Thanks a lot John .
Both options looks good and if i go with second option the DENY Delete on User will super seed the Grant Delete on particular object for the same user.
John Mitchell-245523 - Tuesday, June 13, 2017 4:38 AMTwo options:
(1) Do all DML with stored procedures. Then you don't need to provide permissions on tables or db_datareader/writer access - only EXECUTE on the stored procedures.
(2) Get rid of the db_datareader/writer membership and grant DELETE or INSERT permission on the individual tables as appropriate.John
June 13, 2017 at 4:57 am
Yes, a DENY will always override a GRANT, unless the grantee happens to be a sysadmin or (I think) db_owner.
John
June 13, 2017 at 5:50 am
I have given user Insert ,Update ,Select and then Grant Delete on particular objects and it worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply