Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
razeena
Yak Posting Veteran
54 Posts |
 Posted - 2014-06-12 : 04:46:12
|
| Hi, We are using sqlserver2005 at our liveserver. Due to some third party attacks which caused loss of data, we changed the sql user permission to only read,write and execute.Now, some of the sps in the db contain code to insert into identity column with lineSET IDENTITY_INSERT [tblName] ON insert stmts...SET IDENTITY_INSERT [tblName] OFFThis throwing error asCannot find the object "tblName" because it does not exist or you do not have permissions.Which minimal permission can be given to get the above code work withidentity insert on/off?We have removed the dbo permission due to external attacks.Any help is appreciated.thanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-12 : 08:46:07
|
| "User must own the table or have ALTER permission on the table." from here: http://msdn.microsoft.com/en-us/library/ms188059.aspxIf you don't want to grant that permission to users, and if you have the ability to alter the stored procs, another option would be to use EXECUTE AS clause. You can make the stored procedure execute as owner or just the set identity_insert statements. There are details here: http://msdn.microsoft.com/en-us/library/ms181362.aspxWhile I know that EXECUTE AS can work, I have not investigated the vulnerabilities that might be introduced by doing that - especially since your server is already a target. So if you choose to go that route, please do investigate. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-12 : 11:29:37
|
| Any chance you can change your code so you aren't doing IDENTITY_INSERTs? It seems odd to be doing that in production. |
|
|
|
|
|
|
Minimal permission for identity insert on to work