Run a stored procedure automatically

Question

Post reply

Run a stored procedure automatically

omidshf

SSC Enthusiast

Points: 160
More actions
December 9, 2011 at 2:50 am

#96629

can a stored procedure run automatically when data files attach to a database?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

Divine Flame SSCoach Points: 15943 More actions · Answer 2

Divine Flame

SSCoach

Points: 15943

December 9, 2011 at 3:46 am

#1419173

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.

Sujeet Singh

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

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:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

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.

Lowell SSC Guru Points: 323482 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

omidshf SSC Enthusiast Points: 160 More actions · Answer 5

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

Lowell SSC Guru Points: 323482 More actions · Answer 6

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!