SET XACT_ABORT, NOCOUNT ON USE master go CREATE DATABASE PlayIdent DECLARE @pwd char(37) = convert(char(36), newid()) + 'a' DECLARE @sql nvarchar(MAX) SELECT @sql = 'CREATE LOGIN PlayIdent$owner ' + 'WITH PASSWORD = ' + quotename(@pwd, '''') EXEC(@sql) ALTER LOGIN PlayIdent$owner DISABLE DENY CONNECT SQL TO PlayIdent$owner ALTER AUTHORIZATION ON DATABASE::PlayIdent TO PlayIdent$owner go USE PlayIdent go CREATE ROLE Playrole GRANT EXECUTE ON SCHEMA::dbo TO Playrole CREATE USER Julia WITHOUT LOGIN ALTER ROLE Playrole ADD MEMBER Julia go -------------------------------------------------------------------------------------------------- CREATE TABLE IdentTable(id int NOT NULL IDENTITY, somedata nvarchar(128) NOT NULL, whodidit sysname NOT NULL CONSTRAINT default_IdentTable_whodidit DEFAULT USER, CONSTRAINT pk_IdentTable PRIMARY KEY(id)) go INSERT IdentTable(somedata) SELECT name FROM sys.columns go -------------------------------------------------------------------------------------------------- CREATE PROCEDURE add_identdata @id int, @somedata nvarchar(128) AS DECLARE @curmax int SELECT @curmax = MAX(id) FROM IdentTable IF @id <= @curmax BEGIN RAISERROR('Cannot insert @id %d, as current max is %d', 16, 1, @id, @curmax) RETURN 1 END DBCC CHECKIDENT('IdentTable', RESEED, @id) -- WITH NO_INFOMSGS INSERT IdentTable(somedata) VALUES (@somedata) SELECT id, somedata, whodidit FROM IdentTable WHERE id = scope_identity() go -------------------------------------------------------------------------------------------------- EXECUTE AS USER = 'Julia' EXEC add_identdata 9000, 'Half of what I say is meaningless' go REVERT go -------------------------------------------------------------------------------------------------- DECLARE @perms Management.Permission_list INSERT @perms VALUES ('db_ddladmin') EXEC Management.GrantPermsToSP 'add_identdata', @perms, @debug = 1 go -------------------------------------------------------------------------------------------------- EXECUTE AS USER = 'Julia' EXEC add_identdata 9000, 'Half of what I say is meaningless' go REVERT go -------------------------------------------------------------------------------------------------- ALTER PROCEDURE add_identdata @id int, @somedata nvarchar(128) AS DECLARE @curmax int SELECT @curmax = MAX(id) FROM IdentTable IF @id <= @curmax BEGIN RAISERROR('Cannot insert @id %d, as current max is %d', 16, 1, @id, @curmax) RETURN 1 END EXECUTE AS USER = 'dbo' DBCC CHECKIDENT('IdentTable', RESEED, @id) WITH NO_INFOMSGS REVERT INSERT IdentTable(somedata) VALUES (@somedata) SELECT id, somedata, whodidit FROM IdentTable WHERE id = scope_identity() go ----------------------------------------------------------------------------------------------- DECLARE @perms Management.Permission_list INSERT @perms VALUES ('IMPERSONATE ON USER::dbo') EXEC Management.GrantPermsToSP 'add_identdata', @perms, @debug = 1 go ----------------------------------------------------------------------------------------------- EXECUTE AS USER = 'Julia' EXEC add_identdata 9100, 'All My Loving' go REVERT go ----------------------------------------------------------------------------------------------- USE tempdb go ALTER DATABASE PlayIdent SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE PlayIdent go DROP LOGIN PlayIdent$owner go