
-- there should already be user1 in this database
use pubs
go

CREATE CERTIFICATE signing_cert
   AUTHORIZATION DBO WITH SUBJECT = 'Cert for Signing Procs'
GO

CREATE USER signing_user FOR CERTIFICATE signing_cert
GO

CREATE SCHEMA user1_schema AUTHORIZATION user1
GRANT CREATE PROCEDURE TO user1
GO

-- this table is in DBO schema
-- no access granted to user1
CREATE TABLE signing_table (
 id INTEGER PRIMARY KEY,
 thedata VARCHAR(100)
)
GO
INSERT signing_table VALUES(1, 'data1')
INSERT signing_table VALUES(2, 'data2')
GO

EXECUTE AS USER='user1'
GO
CREATE PROCEDURE user1_schema.get_signing_table
AS
SELECT * FROM signing_table
GO
-- attempt to execute it, fails
EXECUTE user1_schema.get_signing_table
GO
REVERT
GO

-- sign the procedure with the cert
ADD SIGNATURE TO user1_schema.get_signing_table 
  BY CERTIFICATE signing_cert
-- then give the cert's user access to table
GRANT SELECT ON signing_table TO signing_user
GO

EXECUTE AS USER='user1'
GO
-- attempt to execute it, works now
EXECUTE user1_schema.get_signing_table
GO
REVERT
GO
