December 9, 2011 at 2:50 am
can a stored procedure run automatically when data files attach to a database?
December 9, 2011 at 3:16 am
Take a look at triggers. It seems to be what you are looking for.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 9, 2011 at 3:46 am
omidshf (12/9/2011)
can a stored procedure run automatically when data files attach to a database?
If you will elaborate on what you are looking for & for what purpose with some details, we will be able to help you in better manner.
December 9, 2011 at 4:10 am
I imagine something like...
CREATE TRIGGER foo_trigger
ON ALL SERVER
AFTER ALTER_DATABASE
AS
EXEC sp_foo @param1='foo', @param2='bar', ...
GO
Triggers are not my strong suit though, you will want to research valid FOR and AFTER parameters to craft it a bit better.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
December 9, 2011 at 6:17 am
omidshf (12/9/2011)
can a stored procedure run automatically when data files attach to a database?
you mean when a database is attached, right? or do you mean when a database is restored from a file?
you posted in a SQL 2000 forum, I'm hoping that the forum selection was a mistake;
i do not know of a way to detect that event in SQL2000;
I believe in SQL 2005, The DDL event CREATE_DATABASE was not triggered when a database was attached using sp_attach_db; it's been a while, but had investigated that due to some posts here int he past.
I believe in 2008 the events were enhanced and CREATE_DATABASE is triggered when a database is attached, so you could use a ON ALL SERVER DDL trigger to detect the attachment and send yourself an email or log the information or whatever you wanted to do.
if you want to detect when a database is restored, i think you have to switch to event notifications, and use the AUDIT_BACKUP_RESTORE_EVENT event; i'm sure you have to switch to event notifications and not server scoped DDL triggers to capture that event, but i may be wrong.
Lowell
December 9, 2011 at 9:44 am
Thanks for all your replies
i am using MSsql2000 and i want to run astored procedure or function when a database attaches to server.
i want to do this to prevent of attaching a database to server by unauthorised person.
my application runs on themany systems and a perfect person can detach the database , move the files to another system and attach to a new server and finaly open database to know the information.
thank you to guide me
December 9, 2011 at 9:54 am
someone with access can backup the database and restore elsewhere as well; people who would detatch and detatch to get the data would simply be those persons who are not familiar with backups.
the first step is to simply prevent unauthorized access; do you administer the server yourself? if not, there's nothing you can do...itd be out of your hands.
if you are the server admin, then start by taking away the rights of any groups or individuals with sysadmin priviledges.
anyone who has the db_owner role rights has the ability to backup and restore and even drop their database, but it might be the only database they have access to.
physical server security fits in there as well..
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply