Security in Sqlserver 2005 when deleting in management studio

I am using Sqlserver 2005 and now i want to implement security as below

We have access to live database of client to give backend support. Hence there is a possibility that we suuport team can delete data from live database by mistake from management studio which i wan to restrict. How can i do this?

Is it possible to keep some password exclusively for that database so that when deleting/inserting/updating data it will ask password as an alert?

Thanks in advance Usha

asked Jan 07 '10 at 03:48 AM in Default

Usha
110 ● 7 ● 7 ● 8

(comments are locked)

1 answer:

Best will be to have a separate user/account with rights for modifications, so you cannot make a mistake when connecting under you common account.

Also it's possible to setup an Application Role with rights for modifications so you will not have the modify/delete rights unless you set the app role. You can read about the application roles on http://msdn.microsoft.com/en-us/library/ms190998%28SQL.90%29.aspx

So your account will have only read only access to the data and will have no modification right. Another account or application role will have rights for modifications.

answered Jan 07 '10 at 04:35 AM