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