Trigger to add user to db_owner role after db restore

Question

Trigger to add user to db_owner role after db restore

siddhartha20

Say Hey Kid

Points: 699
More actions
April 16, 2008 at 4:42 am

#185541

Hi,
I have requirement where I need to restore production dbs on dev servers. I have an login account "dbservice" on dev server which is memeber of dbcreator,dbsecurityadmin fixed server level role. The requirement is that the restore of prod backup should be run under this accounts context and after the restore this account should be added to db_owner role of the just restored database. This account cannot be given sysadmin privileges.
I first thought of creating a ddl trigger for restore database operation but found out that it is not possible. Then I thought of doing it in a roundabout way where this user would create a login on the server with the dbname embedded in the login. I then created a ddl trigger for create_login event. Where I am parsing the name of the database from new login name and then creating the user in the
database. Something like this.
set @sql = 'use '+ @dbname + ' create user dbservice for login dbservice'
exec(@sql)
The problem is that when I create the login when logged in as the trigger works fine and adds the user to the new db. But when I create the login using dbservice user account
I get this error message. "The server principal "dbservice " is not able to access the database "Test" under the current security context."
So it seems like the code inside the trigger is running under dbservice account's context. Even thought the trigger was created by sa. I even added execute as login ='sa' in create trigger query but that also did not help.
Any help in resolution of the problem would be much appreciated. The basic problem statement is that I should be able to add dbservice login which has only dbcreator and securityadmin permissions to any db on the server after running the restore under this accounts context. The login cannot be assigned sysadmin. The ddl trigger may be one way of addressing it(only if it worked) there may be other simpler solution to this problem but then I ran out of ideas.
One other approach could be to write a sql job running under sa. Which would read from a table to which dbservice can write the database name and then run the sql to create the user in that database. This is my last hope but it is going to complicate things a lot in a project which is already pretty complicated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 1

How often are you restoring these databases? Is it on a regular basis? As in scheduled job regular basis? If so, add an additional step to create the db user/assign rights. Set the job owner to sa.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

siddhartha20 Say Hey Kid Points: 699 More actions · Answer 2

The restore happens through a web based tool, and it happens on demand. The web app runs under dbservice user account. The account cannot be given sa privileges.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 3

I had the same requirement (and more) and searched but couldn't find anything online. I created a custom solution which would store all post restore tasks in a database table and a DDL trigger would execute these post restore scripts. Since DDL triggers don't fire on database restore events, I'm firing an Alter Database command which will raise an ALTER_DATABASE event which in turn will fire up the DDL trigger.

Currently, I'm doing auto-user adds and even executing stored procedures to update configuration data stored in the app. database.

rharkiss SSC Journeyman Points: 93 More actions · Answer 4

You could try:

CREATE TRIGGER [dbo].[PostDBRestore]

ON [msdb].[dbo].[restorehistory]

FOR INSERT

AS

SET NOCOUNT ON

DECLARE @DBName AS VARCHAR(256)

DECLARE @User AS VARCHAR(256)

SELECT @DBName = destination_database_name

, @User = user_name

FROM INSERTED

IF @DBName = 'YourDB' ....

SET NOCOUNT OFF

support.sql Default port Points: 1444 More actions · Answer 5

You ca use Service Broker Technology to capture the Event and raise an action

Review this article and modify it according your requirement.

http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker.

SNM

Try Free Microsoft SQL Server Data Collector & Performance Monitor.

http://www.analyticsperformance.com[/url]

@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

CC-597066 SSCommitted Points: 1637 More actions · Answer 6

I know this is an old thread and may be covered else where but it appeared at the top of the list in google so just in case someone else stumbles across it I thought I would add my experiences.

I have a similar requirement to create users and or logins or adding rights after doing restores from prod to non-prod servers. As suggested above (thanks), I had a look at the trigger on [msdb].[dbo].[restorehistory] but could only get it to work when I manually added a row to the restorehistory table. As it isn't recommended that triggers are put on system tables I didn't look too deeply in to this.

In the end I developed a solution that works for all my servers regardless of version and that is to create an Alert that fires on error 18267 which is the message you see in the errorlog when a database is restored. The alert is then configured to run a SQL job which picks up the database name and user from the restorehistory table and sends me an email to say that a database has been restored.

The script also checks the database name to see if any action is required, e.g. adding users and acts accordingly.

This is great for me as I can now automate tasks that are required after restores and plan to develop it further to check for things like recovery mode as our developers have a habit of leaving restored DB's in Full recovery mode.

Michael-401546 Mr or Mrs. 500 Points: 557 More actions · Answer 7

Just felt I should share the code I used to create my restore alerts

USE [msdb]

GO

/****** Object: Operator [Alert Operator] ******/

EXEC msdb.dbo.sp_add_operator @name=N'Alert Operator',

@enabled=1,

@weekday_pager_start_time=90000,

@weekday_pager_end_time=180000,

@saturday_pager_start_time=90000,

@saturday_pager_end_time=180000,

@sunday_pager_start_time=90000,

@sunday_pager_end_time=180000,

@pager_days=0,

@email_address=N'my.name@mycompany.com',

@category_name=N'[Uncategorized]'

GO

/****** Object: Alert [Restore Success - 18267] ******/

EXEC msdb.dbo.sp_add_alert @name=N'Restore Success - 18267',

@message_id=18267,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object: Alert [Restore Success - 18268] ******/

EXEC msdb.dbo.sp_add_alert @name=N'Restore Success - 18268',

@message_id=18268,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object: Alert [Restore Success - 18269] ******/

EXEC msdb.dbo.sp_add_alert @name=N'Restore Success - 18269',

@message_id=18269,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object: Alert [Restore Success - 4356] ******/

EXEC msdb.dbo.sp_add_alert @name=N'Restore Success - 4356',

@message_id=4356,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object: Alert [Restore Success - Message] ******/

EXEC msdb.dbo.sp_add_alert @name=N'Restore Success - Message',

@message_id=0,

@severity=7,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@event_description_keyword=N'Restore is complete on database',

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO