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
Best Answer
If you are using the same AD user for all your developers I would advise you to start using AD groups and adding your developers to these groups. See here for a Q/A on using one AD login for multiple users. Afterwards you could follow the steps below.
Permissions are granted to all members of the AD group or none.
If you want to grant permissions to some of these users, you would have to add a different ad group or add the users separately.
Afterwards you can grant the update and delete permissions to that group or create a new role with the permissions and add the group / users to it .
Example commands:
If you have deny permissions specified on writing data to the tables then granting corresponding write permissions will not work. DENY has a higher precedence than grant.