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