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 12»»

DDL trigger for RESTORE DATABSE Expand / Collapse
Author
Message
Posted Sunday, February 17, 2008 4:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:21 PM
Points: 53, Visits: 228
Hi,

I have to create a user in each user database after a database has been created or restored in SQL Server 2005. Not able to do so with a DDL trigger for restore operation. Any solution?

Regards,
Ajay Prakash



Kindest Regards,

Ajay Prakash
Post #456766
Posted Sunday, February 17, 2008 8:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:19 PM
Points: 1,393, Visits: 438
unlike create database, restore is not a ddl statement with server scope; read BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a3d3c1a5-6ca0-465b-b1d4-f197dd2b682d.htm



Post #456772
Posted Monday, February 18, 2008 10:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:12 AM
Points: 77, Visits: 428
I do not understand the question. are you attenpting to create a database(or restore the db) and then create the user with a triggered?
am correct?




"We never plan to Fail, We just fail to plan":)
Post #456991
Posted Monday, February 18, 2008 5:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:21 PM
Points: 53, Visits: 228
The new user has to be created after any of the below mentioned operation happens.
1. If a new database has been created
OR
2. If a any database has been retored.



Kindest Regards,

Ajay Prakash
Post #457135
Posted Monday, February 18, 2008 6:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 26, 2014 12:47 PM
Points: 2,665, Visits: 831
Presumably the database being restored is from another server? Otherwise your user would already exist if you were restoring a database that was already present on your server (assuming you have retroactively created this user on all your existing databases).




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 #457151
Posted Tuesday, February 19, 2008 10:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:21 PM
Points: 53, Visits: 228
Scott,

I work in an environment where my team is supporting almost 500 server with all sorts of environment(DEV/UAT/SIT/PROD/DR). These systems are being used by multiple applications, which often requires database restoration from PROD no NON PROD or from NON PROD as well NON PROD. On the top of this application team may tell us to restore databases of 2 years back. I may rollover the users accross the servers once but this will not suffice my requirement. We want to track object creation in any user database and a report goes to each application manager/owner next day. EAch server is capable of sending report on its own.

Therefore I was thinking to have a DDL trigger at each user database level and a server level DDL trigger to cater create and restore database.

Even SQLAgent job does not address my concern accurately becaue the objects can be created just after a restore happnes which will not be rerported. We have a central repository on each server where all database reports it's object creation

Regards,
Ajay Prakash



Kindest Regards,

Ajay Prakash
Post #457508
Posted Tuesday, February 19, 2008 11:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 26, 2014 12:47 PM
Points: 2,665, Visits: 831
In lieu of a trigger, have you considered scripting the restore process, including post-restore steps of creating the user you require? If it is a regular occurrence, maybe implement as a stored procedure, passing db name, path to data files, path to backup file.





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 #457558
Posted Wednesday, February 20, 2008 10:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:21 PM
Points: 53, Visits: 228
Thanks!! I think this may be a good solution to it. Will try not sure if that will be feasible for all to follow. But I will try my best..


Kindest Regards,

Ajay Prakash
Post #458098
Posted Wednesday, July 7, 2010 12:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 42, Visits: 179
See http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx for a full solution including auditing of RESTORE DATABASE and CREATE DATABASE FOR ATTACH in SQL 2005 (and SQL 2008)
Post #948332
Posted Tuesday, July 27, 2010 1:05 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:08 PM
Points: 95, Visits: 1,139
SecretSQuireL (7/7/2010)
See http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx for a full solution including auditing of RESTORE DATABASE and CREATE DATABASE FOR ATTACH in SQL 2005 (and SQL 2008)


It seems that this forum entry does not exist. Or is it the wrong url?

Regards
Dirk


--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #959219
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse