/*
    Author: Filip De Vos
    Date: July 26, 2007
    Purpose: Test allowing normal database users to use SET IDENTITY_INSERT in a stored procedure.

*/

create database identity_insert_test
GO
use identity_insert_test
GO
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 dbo.p_a
go
create procedure dbo.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 dbo.p_a to SimpleLogin
go

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

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

Execute as user = 'SimpleLogin'
exec dbo.p_a
revert;