Need to add user and assign roles automatically, using DDL Trigger, when database is created

Question

I am trying to create an automated process that will add a user and assign them the roles of db_datareader, db_datawriter and db_ddladmin whenever a database starting with PA is created. I have created a trigger that I can run that perfectly creates what I want, but when the user creates the database through an application, the trigger throws a permissions error.

I've tried splitting the trigger up to call a stored procedure (which has the CREATE USER and ALTER ROLE scripts), then use a certificate signature, but when I run the test with a temp user it errors out at the stored procedure with the message: 'The server principal "tLogin" is not able to access the database "PATest" under the current security context.'.

I'm assuming that I haven't given the certificate user the correct permissions, but I'm stuck on how to do that. Has anyone tried to do this before?

Aleksey Vitsko · Answer 1 · 2024-07-29T18:52:25.227

Adding a user to a model database and assigning it to required database roles would be easiest, but since you have a requirement:

...whenever a database starting with PA is created...

Means you don't want to expose other created databases to that user.
In that case, try below steps:

create a server-level certificate in a master database
create a login from this certificate, grant that login CONTROL SERVER permission (or add to a sysadmin role).
sign your stored procedure by this certificate

This way certificate will have enough permissions to execute logic in the stored procedure.

p.s. Also note that server-level DDL can have execute as <...>. You can set it to execute on behalf of a user (login) that has sysadmin privileges.

Ronaldo · Answer 2 · 2024-08-12T11:22:58.973

I believe you can use a DDL Trigger like this (just change the login and user name):

CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    DECLARE @DatabaseName nvarchar(128);
    DECLARE @SQL nvarchar(4000);
SELECT @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)');

IF @DatabaseName LIKE 'PA%'
BEGIN

    SET @SQL = '
        USE ' + @DatabaseName + ';
        CREATE USER [ronaldo] FOR LOGIN [ronaldo];
        ALTER ROLE db_datareader ADD MEMBER [ronaldo];
        ALTER ROLE db_datawriter ADD MEMBER [ronaldo];
        ALTER ROLE db_ddladmin ADD MEMBER [ronaldo];';

    EXEC (@SQL);
END

GO

This trigger relies on the EVENTDATA Function to get the name of the created database and verify your condition about the 'PA%' naming convention.

It worked for me without any permission erros, so I assumed the trigger executed as dbo (the login with the permission to create the database becomes the owner of the database created), Since the user you desire to create resides within the database, no permission error is thrown.

That is my theory, but I have to do more tests and check documentation to fully understand and confirm that.

Source: Trigger actions on database creation or attach

Need to add user and assign roles automatically, using DDL Trigger, when database is created

2 Answers2