title: Execute sp_addrolemember on specified database in SQL Server 2008 (or an alternative method)
body: <p>I am trying to set a trigger on the dbo.restorehistory table in an attempt to verify user roles after database restores. The trigger simply queries 'destination_database_name' when a restore logs to the history table. I then attempt to run 'EXEC sp_addrolemember' on the database in question. Apparently, however, the procedure does not exist outside the scope of the database, i.e. it is unavailable inside the trigger.</p>
  
  <p>So does anyone have a suggestion as to how to manage this task? Apart from the obvious fact that my method does not work, there could be better methods entirely.</p>
  
  <p>In very short terms, I need to be able to add a user and set him to db_owner after restores.</p>
  
  <p>The problem with these restores is that they are executed remotely from a 3rd party application which we have no control over. When they create a new database, they execute a restore to populate the database with default data and thus overwrite the user roles that we have specifically set, even though we have them specified in the model database. So...</p>
  
  <ol>
  <li>A third party program creates a database</li>
  <li>The database inherits users from the model database</li>
  <li>The third party program overwrites the users by restoring a default database</li>
  <li>I need to have those users recreated and have their roles applied</li>
  </ol>
  
  <pre>
  
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      -- =============================================
      CREATE TRIGGER [dbo].[AfterRestore] 
         ON  [msdb].[dbo].[restorehistory]
         AFTER INSERT
      AS 
      BEGIN
         SET NOCOUNT ON;
         DECLARE @DatabaseName AS VARCHAR(100)
           SELECT @DatabaseName = destination_database_name
           FROM INSERTED
           CREATE USER [XYZZY Users] FOR LOGIN [domain\xyzzyusers]
           EXEC sp_addrolemember 'db_owner', 'XYZZY Users'
      END
      GO
  
  </pre>