Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger to add user to db_owner role after db restore Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2008 4:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 14, 2011 4:53 AM
Points: 97, 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.








Post #485537
Posted Wednesday, April 16, 2008 10:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:13 PM
Points: 2,665, Visits: 840
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
Post #486095
Posted Wednesday, April 16, 2008 10:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 14, 2011 4:53 AM
Points: 97, 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.


Post #486100
Posted Thursday, April 16, 2009 7:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, 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.
Post #698423
Posted Tuesday, November 24, 2009 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:04 AM
Points: 1, 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
Post #824057
Posted Friday, November 27, 2009 9:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 2:07 PM
Points: 70, 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
Post #825686
Posted Wednesday, October 5, 2011 3:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:32 PM
Points: 51, Visits: 898
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.
Post #1186242
Posted Monday, January 6, 2014 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 4, 2014 2:52 PM
Points: 12, Visits: 297
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
Post #1528087
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse