question

Usha avatar image
Usha asked

Security in Sqlserver 2005 when deleting in management studio

Hi

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

sql-server-2005securityssms
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.