One result of having the correct care with the access permissions in our databases is the need to avoid the DBO permission even to the users responsible for the database model.
Using the database roles such as ddl_admin or even in more grainy ways it’s easy to achieve this goal.
However, it’s more complex when we would like to use schemas in the database model. The creator of the schema becomes its owner and the existence of several schema owners in the model creates a problem we call ‘broken ownership chain’, a truly hell to DBA’s.
One possible solution would be ask to every schema creator to give the ownership of their schemas to the DBO user, all schemas will be owned by DBO. However, even the members of ddl_admin database role hasn’t enough permission to give the ownership of schemas to the DBO.
The solution for this problem is create a DDL trigger to intercept the creation of schemas and change the ownership of the schema to the DBO.
The trigger will be like this :
execute AS
‘dbo’ FOR create_schema AS
DECLARE @EventData xml = eventdata();
declare @owner sysname
SELECT @owner=@EventData.value(‘(/EVENT_INSTANCE/OwnerName)[1]’,‘sysname’)
IF @owner<>‘dbo’
BEGIN
DECLARE @sql varchar(500)
DECLARE @sch sysname
SELECT @sch=@EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’,‘sysname’)
SELECT @sql=‘alter authorization on schema::’ + @sch + ‘ to dbo’
EXEC(@sql)
END
Once the trigger is created, everytime a user creates a new schema the ownership of the schema will be changed to DBO user.
You can also create this trigger inside the model database, so every new database created on the server will already be created with the trigger.
Load comments