SQL Server Update Permissions

Question

I'm currently working with SQL Server 2008 R2, and I have only READ access to a few tables that house production data.

I'm finding that in many cases, it would be extremely nice if I could run something like the following, and get the total record count back that was affected :

USE DB
GO

BEGIN TRANSACTION
UPDATE Person
SET pType = 'retailer'
WHERE pTrackId = 20 
AND pWebId LIKE 'rtlr%';

ROLLBACK TRANSACTION

However, seeing as I don't have the UPDATE permission, I cannot successfully run this script without getting :

Msg 229, Level 14, State 5, Line 5
The UPDATE permission was denied on the object 'Person', database 'DB', schema 'dbo'.

My questions :

• Is there any way that my account in SQL Server can be configured so that if I want to run an UPDATE script, it would automatically be wrapped in a transaction with an rollback (so no data is actually affected)

I know I could make a copy of that data and run my script against a local SSMS instance, but I'm wondering if there is a permission-based way of accomplishing this.

Answer 1

I don't think there is a way to bypass SQL Server permissions. And I don't think it's a good idea to develop on production database anyway. It would be much better to have development version of the database you work with.

---

If the number of affected rows is all you need then you can run select instead of update.

For example:

select count(*)
from Person
where pTrackId = 20 
AND pWebId LIKE 'rtlr%';