Logo Questions Linux Laravel Mysql Ubuntu Git Menu

SQL Server Update Permissions

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 :


SET pType = 'retailer'
WHERE pTrackId = 20 
AND pWebId LIKE 'rtlr%';


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.

like image 297
X3074861X Avatar asked Mar 22 '23 09:03


1 Answers

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%';
like image 193
user1455836 Avatar answered Apr 01 '23 20:04