0

I put together the following SQL script to create a trigger when a new DB is created, to grant db_owner permissions to a specific account:

    CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
DECLARE @DatabaseName NVARCHAR(128), @SQL NVARCHAR(4000)

SELECT  @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)');
SET @SQL = '
    USE ' + @DatabaseName + ';
    EXEC sp_addrolemember N''db_owner'', N''[accountname]'';'

EXEC(@SQL)

However, I get the following error when I try to create a new DB to test this Trigger:

Message: User or role '[accountname]' does not exist in this database. Could not find database ID 45, name '45'. The database may be offline. Wait a few minutes and try again.

I put this together using some examples found on the web. It appears that the Trigger is occurring right when DB is being created, instead of running after DB has been created. Is there a way to delay it?

James Z
  • 12,209
  • 10
  • 24
  • 44
SomeDev
  • 1
  • 1
  • You have to add the user to the database first via `CREATE USER [accountname] FOR LOGIN [accountname]`, at which point you can then add the user to the `db_owner` role, though I would suggest you use `ALTER ROLE [db_owner] ADD MEMBER [accountname]` instead of `sp_addrolemember` as [sp_addrolemember](https://msdn.microsoft.com/en-us/library/ms187750.aspx) has been marked as deprecated. – John Eisbrener Mar 14 '17 at 16:07
  • Thank you. Adding the CREATE USER statement before granting it the 'dbowner' role worked. I did replace the stored proc per your suggestion with the ALTER statement and that worked as well. Good to know that sp_addrolemember will be deprecated soon. I still see it on our SQL Server 2012. – SomeDev Mar 14 '17 at 16:51
  • at the end did my answer help? – Vladislav Oct 16 '18 at 21:19
  • @SomeDev please, could you share your final script which has worked? – AdemirP Sep 04 '20 at 13:26

1 Answers1

0

Few things to be mentioned here:

  1. The trigger is executed AFTER the database is created as this is the default behaviour
  2. Not being able to find the database may point to lack of required permissions. Make sure you have enough permission by impersonating as a user that has been granted enough permissions i.e. some database owner. Use the WITH EXECUSE AS clause.
  3. Make sure you have the "[accountname]" existing at the new DB or at the server level (depends what kind of account you are trying to add). You can add a database user, database role, Windows login, or Windows group.
    References:
    https://msdn.microsoft.com/en-us/library/ms189799.aspx
    https://msdn.microsoft.com/en-us/library/ms187750.aspx
    SQL Server 2008 - Does a trigger run with the same permissions as the login/user?
Community
  • 1
  • 1
Vladislav
  • 2,772
  • 1
  • 23
  • 42