Using SET IDENTITY_INSERT as a normal database user in SQL Server 2005.

In earlier versions of Microsoft SQL Server it was impossible to grant "SET IDENTITY_INSERT" rights to a normal user. To have permission to do an identity_insert (even as part of a stored procedure) you needed to grant the user ddl admin rights which is quite a heavy permission to grant to a normal user.

Luckily this problem can be solved in SQL Server 2005 by using Certificates and code signing.

Let's set up a demo environment.

create database identity_insert_test
use identity_insert_test
exec sp_addlogin 'SimpleLogin', 'PasswordForSimpleLogin'
GO
exec sp_grantdbaccess 'SimpleLogin', 'SimpleLogin'
GO

if exists(select * from sys.objects where object_id = object_id('dbo.a')) 
   drop table dbo.a
create table dbo.a (keyfield int identity, somevalue varchar(10))
GO

if exists(select * from sys.objects where object_id = object_id('p_a')) 
  drop procedure p_a
go
create procedure p_a
as
set identity_insert dbo.a on
insert into dbo.a (keyfield, somevalue) values (2,'nice')
set identity_insert dbo.a off

return (0)
go

grant exec on p_a to SimpleLogin
go

When we run the stored procedure with the simple "no rights" login we quickly see the following error

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

(Make sure you run the "revert;" part of the statement to revert to your login after the error.)

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

To fix this annoying problem we need to create a few more objects in the database. First of all we need a master key which will be used to encrypt the certificate. Then we create a certificate, a login based on the certificate and we grant the "ALTER ANY SCHEMA" permission to the login. And finally we will sign the stored procedure with the schema login.

CREATE MASTER KEY ENCRYPTION BY password = '**somethingspecial**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
GRANT ALTER ANY SCHEMA TO SchemaCertUser
ADD SIGNATURE TO p_a BY CERTIFICATE SchemaCert
GO

Now when we rerun the stored procedure as our simple login we see that everything works as expected.

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

The ability to sign stored procedures gives you the power to build a much more secure SQL Server Database system. For example you can sign stored procedures that use dynamic sql and remove the need to grant select rights on the underlying tables.

I have not (yet) benchmarked the performance impact of signing stored procedures, but I suspect the overhead will not be huge.

You can download the complete test script and try it out.