February 17, 2008 at 4:04 pm
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
Ajay Prakash
February 17, 2008 at 8:54 pm
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
February 18, 2008 at 10:33 am
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":)
February 18, 2008 at 5:44 pm
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.
Ajay Prakash
February 18, 2008 at 6:33 pm
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).
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 19, 2008 at 10:19 am
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
Ajay Prakash
February 19, 2008 at 11:44 am
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.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 20, 2008 at 10:09 am
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..
Ajay Prakash
July 7, 2010 at 12:40 am
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)
July 27, 2010 at 1:05 am
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
July 27, 2010 at 2:53 am
http://www.sqlservercentral.com/Forums/Topic947481-391-1.aspx
still exists and is the same. I posted it in SQL2005 and SQL 2008 forum but now is only in SQL 2008. It will also be published as an article on 17 Aug 2010.
July 27, 2010 at 3:08 am
Thx a lot.
I already found it by searching in all your posts as well. 🙂
I´m looking forward to read the full article.
Your solution has a great potential and it´s a great work so far.
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
July 27, 2010 at 4:59 am
Thanks for the compliment! I'll treat myself to a hazelnut. The article is no different to this post. The code is also complete with no planned updates at this stage. I already spent loads of time on it when I could have been collecting nuts for the winter! It is operational at my site on tens of servers and is functioning fine so far (a few months). I know I don't explain the actual DatabaseAuditTrigger or ServerAuditTrigger much in the article but that was the easy part and they are well commented. The hard part was making it self maintaining/installing and coping with database restores and attaches. You can find explanations of basic triggers in many other places.
Let me know if you have any problems with it. The main problems I have is when someone else installs it but does not create the required folder (or file permissions) for the two job logs.
October 1, 2012 at 6:09 am
Dear Mr.Ajay,
Did you finally find the solution for your trigger? i myself am in need for the same.
I have created the trigger for CREATE db but not for restore and attach.
Can you please help !
Thanks
T
October 10, 2012 at 11:59 pm
tauseef.jan (10/1/2012)
Did you finally find the solution for your trigger? i myself am in need for the same.I have created the trigger for CREATE db but not for restore and attach.
have you read this ? http://www.sqlservercentral.com/Forums/Topic947481-391-1.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply