Post reply

Permissions to execute SET IDENTITY_INSERT

  • May 12, 2004 at 2:13 am

    #107921

    Hi All,

    the situation is: we have a table with identity column.

    For example:

    -- Create products table.

    CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

    GO

    -- Inserting values into products table.

    INSERT INTO products (product) VALUES ('screwdriver')

    INSERT INTO products (product) VALUES ('hammer')

    INSERT INTO products (product) VALUES ('saw')

    INSERT INTO products (product) VALUES ('shovel')

    GO

    select @@identity -- return value is 4

    A User1 (members of the sysadmin ) has a permissions to execute SET IDENTITY_INSERT products ON and a User2(members of the db_datawriter) needs to insert rows with an explicit ID value.

    How can I transfer permissions to execute SET IDENTITY_INSERT from User1 to User2 without changing database  role of User2 ? I need transfer only this one permission, not other else. It is posible ?

    Regards,

    NorbertA

  • May 12, 2004 at 5:01 am

    #506109

    since execute permissions to SET IDENTITY_INSERT default to sysadmin server role and db_owner and db_ddladmin db roles and the object owner if User2 is not part of any of these roles then the SET will not work...the following workaround might work

    Write a stored proc for the insert as follows:

    CREATE PROCEDURE dbo.IDInsert

    @IDVal int,@ProductVal Varchar(20)

    AS

    SET IDENTITY_INSERT dbo.Products ON

    INSERT dbo.Products(ID,ProductVal)

    SELECT @IDVal,@ProductVal

    SET IDENTITY_INSERT dbo.Products OFF

    SELECT * FROM dbo.Products WHERE ID = @IDVal

    GO

    and ask User2 to run the stored proc for inserts (like: Exec dbo.IDInsert 10,'hacksaw')...

    HTH...

  • May 13, 2004 at 2:23 am

    #506249

    Winash, your solution won't work. The user would still get the error "The current user is not the database or object owner of table 'dbo.Products'. Cannot perform SET operation.".

    I see no other solution then:

    a) adding the user to sysadmin, db_owner or db_ddladmin role.

    b) create separate tables owned by each user (this is very weird)

    c) stop using Identity (generate values with a trigger, only if they are not specified)

    Razvan

  • May 13, 2004 at 3:07 am

    #506258

    Razvan is right...my solution won't work...(from next time I'll try to test things out before posting.. ) 

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply