CODEWITHSUNDEEP
sql-server
Mari yah
Mari yah

Reputation: 203

SET IDENTITY_INSERT not working on specific account

I have an Admin_Acct which is the DB_Owner where I have grant User_Acct a Write Permission to a table SrcTable_Arc. I have this query: 

SET IDENTITY_INSERT SrcTable_Arc ON
     INSERT INTO SrcTable_Arc (
        TxnDate
        ,TxnName
        ,ServerName
        ,ReplyReceived
        ,HistoryId)
    SELECT 
        TxnDate
        ,TxnName
        ,ServerName
        ,ReplyReceived
        ,HistoryId
    FROM SrcTable
    WHERE TxnDate = '2019-11-08 10:06:09.888'
    ORDER BY TxnDate Desc
     SET IDENTITY_INSERT SrcTable_Arc OFF

This query works when I use the Admin_Acct. But, when I try to run this query using user_Acct, it gives an error:

Msg 1088, Level 16, State 11, Line 3
Cannot find the object "SrcTable_Arc" because it does not exist or you do not have permissions.

I have checked the permissions of this account to this table and it has the permissions already.

Anyway, when I do simple Update, Insert and Delete to that table using User_Admin acct, it works.

Any advice?

Upvotes: 0

Views: 961

Answers (2)

Roger Wolf
Roger Wolf

Reputation: 7692

It is possible that the user account doesn't have necessary permissions on the SrcTable_Arc table for the identity_insert. Documentation states it clearly that:

Permissions

User must own the table or have ALTER permission on the table.

Personally, I wouldn't recommend granting this kind of permission directly to the user. Instead, wrap your code into a stored procedure and make sure that procedure's schema owner does have all the necessary permissions, then grant the execute on that procedure to the user.

Upvotes: 1

Shep
Shep

Reputation: 628

You could try turning on identity_insert prior to the insert command?

SET IDENTITY_INSERT SrcTable_Arc ON

SET IDENTITY_INSERT SrcTable_Arc ON     
INSERT INTO SrcTable_Arc (
            TxnDate
            ,TxnName
            ,ServerName
            ,ReplyReceived
            ,HistoryId)
        SELECT 
            TxnDate
            ,TxnName
            ,ServerName
            ,ReplyReceived
            ,HistoryId
        FROM SrcTable
        WHERE TxnDate = '2019-11-08 10:06:09.888'
        ORDER BY TxnDate Desc
         SET IDENTITY_INSERT SrcTable_Arc OFF

Upvotes: 1

Related Questions