x

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

more ▼

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

Usha gravatar image

Usha
110 7 7 8

(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

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1945
x161
x125

asked: Jan 07, 2010 at 03:48 AM

Seen: 1200 times

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