SQL Server Update Permissions

Question

I am currently working with SQL Server 2008 R2, and I only have READ access to several tables that store production data.

I find that in many cases it would be very nice if I could run something like the following and get the total number of entries that were affected:

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

However, seeing that I do not have UPDATE permission, I cannot successfully run this script without receiving:

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

My questions:

• Is it possible to configure my account in SQL Server so that if I want to run an UPDATE script, it will be automatically completed by a rollback transaction (so that in fact the data will not be affected)

I know that I can make a copy of this data and run my script for a local SSMS instance, but I am wondering if there is a way to accomplish this.

Answer 1

I don’t think there is a way around SQL Server permissions. And I do not think it is a good idea to develop in a production database. It would be much better to have the database development version that you are working with.

---

If the number of rows affected is all you need, you can run select instead of updating.

For instance:

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

Answer 2

If you are only after the number of rows that will be affected by this update, it will be the same number of rows that currently matches the WHERE .

So, you can simply run the SELECT as such:

 SELECT COUNT(pType) FROM Person WHERE pTrackId = 20 AND pWebId LIKE 'rtlr%'; 

And you get the affected potential rows.

Answer 3

1.First login as admin in sqlserver
2.Goto login-> your name-> Check Roles.
3.IF u has write access, then you can perform the above task.
4. If you do not provide access to the recording.

Answer 4

If you need to perform an upgrade, you can write a stored procedure, accept dynamic SQL as a string (your UPDATE query), and wrap dynamic SQL in a transaction context, which is then rolled back. Your account can then be granted access to this stored procedure.

Personally, I think it's a terrible idea and incredibly dangerous - some queries come out of such transaction contexts (e.g. ALTER TABLE). You may be able to somehow block them, but it will still be a security / audit issue.

I recommend writing a query to count the relevant rows:

 SELECT COUNT(*) FROM --tables WHERE --your where clause -- any other clauses here eg GROUP BY, HAVING ...