SQL Server – Using Active Directory Group as Schema

active-directorypermissionsschemasql-server-2008-r2

In SQL Server 2008 R2 we have a database that is being maintained by a team of developers.
Each of them should have permissions to perform DDL operations such as adding a column or creating a table.
However, we do want to log all the DDL actions done by a developer.

Using one user for everyone is not an option due to poor audit, using multiple users (Active Directory users) results their tables having separate schema names and that's untidy.

Is there a way to use Active Directory group as the schema to enjoy both worlds?

Best Answer

No, an Active Directory group cannot be the schema, though I suppose you could name a schema with the same text as your AD group, but that does not get you more auditability.

If individual logins are all members of the AD group, they still appear in the database by default as their login name, not the AD group name. (You can, of course, rename the database users to different names, but I do not see how that would really help you.)

However, using 'multiple users' (i.e one login and user per person) does not mean that they will all create their own schema. You have considerable control over this behavior.

When you create the user's you can assign them to a default schema, such as dbo. Likewise, if you need to set that up after the fact you can:

ALTER USER userName  
     WITH DEFAULT_SCHEMA = schemaName

In addition, you can create an audit table and use a DDL trigger to track the changes being made to the database by tracing the details you want to know about. You can also filter out nuisance changes, such as disabling and enabling indexes and so forth.

Sample DDL Audit Trigger:

CREATE TRIGGER [AuditDDLChanges] 
    ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
SET NOCOUNT ON
DECLARE @Data XML

DECLARE 
    @EventType NVARCHAR(128),
    @DatabaseName NVARCHAR(128),
    @SchemaName NVARCHAR(128),
    @ObjectName NVARCHAR(128),
    @ObjectType NVARCHAR(128),
    @LoginName NVARCHAR(128),
    @TSQLCommand NVARCHAR(MAX),
    @IgnoreMarker NVARCHAR(1)

SET @Data = EVENTDATA()

SELECT 
    @DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
    @SchemaName =   @data.value('(/EVENT_INSTANCE/SchemaName)[1]',   'nvarchar(128)'),
    @ObjectName =   @data.value('(/EVENT_INSTANCE/ObjectName)[1]',   'nvarchar(128)'),
    @ObjectType =   @data.value('(/EVENT_INSTANCE/ObjectType)[1]',   'nvarchar(128)'),
    @EventType =    @data.value('(/EVENT_INSTANCE/EventType)[1]',    'nvarchar(128)'),
    @LoginName =    @data.value('(/EVENT_INSTANCE/LoginName)[1]',    'nvarchar(128)'),
    @TSQLCommand =  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',  'nvarchar(max)')

-- Clause to avoid tracking things you do not want in the event table
IF ( (IS_SRVROLEMEMBER('sysadmin',@LoginName) = 1) OR
     (@EventType LIKE '%STATISTICS') 
   )
   BEGIN
      SELECT @IgnoreMarker = 1 
   END
ELSE
   BEGIN
        INSERT AuditDatabase.dbo.ChangeLog
           (EventType, 
            PostTime,
            SPID,
            ServerName,
            LoginName,
            UserName,
            DatabaseName,
            SchemaName,
            ObjectName,
            ObjectType,
            TSQLCommand) 
           VALUES 
           (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'), 
            @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int'), 
            @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)'), 
            @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') ) ;
      END
GO
Related Question