SQL Server Performance

Granting SET Identity_Insert

Discussion in 'General Developer Questions' started by JackW, Sep 11, 2003.

  1. JackW New Member

    Is there any way to grant the SET IDENTITY_INSERT ON permission to a user? Everything I've tried or read indicates that the user must be the table owner, the dbo or db_ddladmin. Thanks in advance.

    - Jack
  2. gaurav_bindlish New Member

    Try creating a stored procedure which takes the table name as input parameter and sets the setting. The procedure must be created by sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, or the object owner. The creator should grant permission on the stored procedure to your login.


    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. bambola New Member

    try wrapping it with a stored procedure, granting execute permission on that stored.

    create procedure grant_set_identity
    as
    SET IDENTITY_INSERT database_name.owner.table_name ON

    create procedure revoke_set_identity
    as
    SET IDENTITY_INSERT database_name.owner.table_name OFF

    grant execute on grant_set_identity to login_name
    grant execute on revoke_set_identity to login_name

    Bambola.

  4. gaurav_bindlish New Member

    Great Minds Think Alike...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. JackW New Member

    Thanks, but I already tried wrapping the command in a proc created by sa and allowing the permissions to inherit. I still got an error stating that the current user was not the dbo or owner of the object.
  6. gaurav_bindlish New Member

    Dis you grant execute permission on the table to all users?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. bambola New Member

    Not on the table, Gaurav, but on the procedure <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I tried the code above and it does not produce an error... Are you doing anything else in that procedure? what is the exact error message? Could you script your stored procedure + permissions and post is here? <br /><br />Bambola.
  8. gaurav_bindlish New Member

    Yeah.. Yeah.. whatever... <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />U know what I meant....<br /><br />Gaurav<br /><i>Moderator<br /><font size="1">Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard</i></font id="size1">
  9. JackW New Member

    -- Created the proc as sa.
    -- dud01 is the table name.
    -- dud is the test login.

    CREATE PROC II_on
    as
    SET IDENTITY_INSERT pubs.dbo.dud01 ON
    GO

    GRANT EXECUTE ON II_on to dud
    GO

    When I login as dud run the command:
    EXEC II_on

    Result:
    Server: Msg 8104, Level 16, State 1, Procedure II_on, Line 3
    The current user is not the database or object owner of table 'pubs.dbo.dud01'. Cannot perform SET operation.

    Thanks again
  10. bambola New Member

    silly me... I used it on a database that with a user that has db_owner permission so I didn't see that. Sorry about that!

    Bambola.
  11. Twan New Member

    You can't permit identity_insert to anyone else... You'd have to either remove the identity field and always insert it or leave it there and never insert it

    Cheers
    Twan

Share This Page