DDL trigger fetching and weighting operation

In the recent docking, the business (data mining colleague) needs to control some accounts to the table level, allowing only some accounts to access the specified tables.

And these people are uncertain, and the changes will be more frequent. After communicating with the colleague, the following scheme is adopted: to give this colleague the db_securityadmin role account for this database, this account can give other users table-level permissions at the library level. Create a new database, and add a log table in which to record the corresponding operation. Assign this account to log table operation (grant Insert) The remaining permissions do not have a DDL trigger at the new library level, and this trigger gets the appropriate permission action record (Grant,deny,revoke) Detailed steps and SQL code are as follows:

Create Database [Triggerlog];GO Use [Triggerlog];GOCREATE TABLE [dbo].[Authority_log](    [ID] [bigint] IDENTITY(1,1) not NULL,    [Posttime] [datetime]  not NULL DEFAULT(getdate()),    [Db_user] [nvarchar]( $) not NULL DEFAULT("'),    [DBNAME] [nvarchar]( -) not NULL DEFAULT("'),    [EventType] [nvarchar]( -) not NULL DEFAULT("'),    [Tsqlcommand] [nvarchar](Max) not NULL DEFAULT("'), CONSTRAINT [Pk_authority_log] PRIMARY KEY CLUSTERED([ID] ASC))  on [PRIMARY] GO

Build a library & build a table

--give account Logging permissions * * * necessary, otherwise the trigger will not function, and the permission operation cannot be performed * * * Use [Triggerlog]GOCREATE USER [Test]  forLOGIN[Test]            --Create a new login account in the log library for the corresponding userGOGRANT INSERT  on [dbo].[Authority_log]  to [Test]    --give users permission to insert log table dataGO

give account logging permissions

--Create a new DDL trigger on the desired open-weight library Use [TestDB]        --If you have multiple libraries, deploy one on each libraryGOIF EXISTS(SELECT *  fromSys.triggersWHEREName=N'Tr_train_authority_monitor'  andParent_class_desc=N'DATABASE')    DROP TRIGGERTr_train_authority_monitor on DATABASEGOCREATE TRIGGERTr_train_authority_monitor on DATABASE     forGrant_database,deny_database,revoke_database as    DECLARE @dataXMLSET @data =EVENTDATA ()INSERTTriggerlog.dbo.[Authority_log](Posttime, Db_user,dbname, EventType, Tsqlcommand)VALUES        (GETDATE(),CONVERT(nvarchar( -),Current_User),db_name(),       @data. Value ('(/event_instance/eventtype) [1]','nvarchar (+)'),        @data. Value ('(/event_instance/tsqlcommand) [1]','nvarchar (+)') ) ;GO

New DDL Trigger

--Create a new login and assign a db_securityadmin role to the desired open-weight library Use [TestDB]        --If you have multiple libraries, deploy one on each libraryGOCREATE USER [Test]  forLOGIN[Test]GOALTERROLE[db_securityadmin] ADDMEMBER[Test]GO

New login and give db_securityadmin roleAt this point, the relevant permission to open and monitoring has been deployed. Test results:

DDL trigger fetching and weighting operation