We have created an "AdminUser" with the bare minimum rights for the activites it will need to do. One thing the "AdminUser" will need to do is a "SET IDENTITY_INSERT ON/OFF".
I understand that for this user to be able to do this they will need ddl_admin rights. But that gives them all kinds of permission that I don't want this user to have.
Is there a specific permission I can give the user so they can do the "IDENTITY_INSERT"
I tried "GRANT create table to AdminUser" but that didn't work. I tried "GRANT alter any database DDL trigger to AdminUser" but that didn't work either. I'm looking for something more specific than "ddl_admin".
Thanks.
Trish
SET IDENTITY_INSERT
Mihailik
Although this post is a little old, I've just come across the same problem myself and this post has solved my problem. However, I've done it a slightly different way and I thought I would post it here in case it is of use to other readers.
The solution is the same as the one that solves the ownership chaining problem when using sp_sqlexec from within a stored procedure. It involves creating a certificate and an associated user. Grant all the permissions you need to the user and sign all the affected stored procs with the certificate.
E.g. (note, in the example below, it is assumed there exists a database master certificate)
USE MyDB
OPEN
MASTER KEY DECRYPTION BY PASSWORD='{password}'CREATE
CERTIFICATE MyExecCert WITH SUBJECT = 'My Certificate'CREATE USER
MyExecUser FROM CERTIFICATE MyExecCertThen for every procedure or permission that you wish to grant (in this case a proc or trigger that contains the SET IDENTITY_INSERT statement)
GRANT ALTER ANY SCHEMA TO MyExecUser
ADD SIGNATURE TO MyProcOrTrigger BY CERTIFICATE MyExecCert
Note that the ADD SIGNATURE line needs to be re-run whenever the proc or trigger is recreated or even altered.
Cheers,
~swg
Behnam_acc
It's sufficient to have ALTER on the table to turn IDENTITY_INSERT on for it. I will open a bug to update the BOL article with this information.
Thanks
Laurentiu
Daniel B.
Is there a more granular way to grant this right
I have a situation where a procedure was used in the past to peform identity inserts when needed, and the user role was granted right to the proc. Now on 2005, this does not work, as the user has no rights to the underlying table, and the new security model stops the proc from setting IDENTITY_INSERT ON because the user does not have the ALTER SCHEMA right.
What are the implications from a security perspective
LCSandan
ANSWER:
GRANT ALTER ANY SCHEMA TO AdminUser