Granting SET Identity_Insert

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

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

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.

Great Minds Think Alike...

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

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.

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

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.

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">

-- 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

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.

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