SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger to add user to db_owner role after db restore


Trigger to add user to db_owner role after db restore

Author
Message
siddhartha20
siddhartha20
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 114
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.



thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3724 Visits: 939
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
siddhartha20
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 114
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
SA-1
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1559 Visits: 944
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
rharkiss
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 60
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
support.sql
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 32
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

@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector
CC-597066
CC-597066
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1089
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
Michael-401546
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 348
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

EXEC msdb.dbo.sp_add_notification
@alert_name=N'Restore Success - 18267'
, @operator_name=N'Alert Operator'
, @notification_method = 1
GO
EXEC msdb.dbo.sp_add_notification
@alert_name=N'Restore Success - 18268'
, @operator_name=N'Alert Operator'
, @notification_method = 1
GO
EXEC msdb.dbo.sp_add_notification
@alert_name=N'Restore Success - 18269'
, @operator_name=N'Alert Operator'
, @notification_method = 1
GO
EXEC msdb.dbo.sp_add_notification
@alert_name=N'Restore Success - 4356'
, @operator_name=N'Alert Operator'
, @notification_method = 1
GO
EXEC msdb.dbo.sp_add_notification
@alert_name=N'Restore Success - Message'
, @operator_name=N'Alert Operator'
, @notification_method = 1
GO
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search