Need to add user and assign roles automatically, using DDL Trigger, when database is created
2
votes
2
answers
230
views
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?
Asked by Eric
(21 rep)
Jul 18, 2024, 04:40 PM
Last activity: Aug 12, 2024, 11:22 AM
Last activity: Aug 12, 2024, 11:22 AM