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

more ▼

asked Jan 07, 2010 at 03:48 AM in Default

avatar image

110 7 9 12

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jan 07, 2010 at 04:35 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 07, 2010 at 03:48 AM

Seen: 1689 times

Last Updated: Feb 17, 2010 at 07:17 AM

Copyright 2018 Redgate Software. Privacy Policy