triggers sysprocess system table

Question

triggers sysprocess system table

Ms CRB

Mr or Mrs. 500

Points: 528
More actions
February 14, 2005 at 8:21 am

#85083

I’ve been asked if I can get a listing of users and the last time that they have accessed the database through an application. I do not want to use sql profiler,I am trying to create a trigger to accomplish this. The sysprocess system table seems to store the fields that I need.I used sp_configure and set allow update to 1. I received the error Create trigger permissions denied on object ‘systemtable’ , database ‘master’, owner ‘dbo’. Does anyone know a work around, or have any other suggestions on how to accomplish this?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

I don't think you can trigger sysprocesses as this is a fake table. It is a memory structure that only materialized when queried:

select

objectproperty(object_id('sysprocesses'),'Tableisfake')

-----------

1

(1 row(s) affected)

What about a job doing this

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ms CRB Mr or Mrs. 500 Points: 528 More actions · Answer 2

what will this select statement do?

select

objectproperty(object_id('sysprocesses'),'Tableisfake')

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

it means you can't put a trigger on that table (on any system table for that matter).

You'll have to do snapshots of the table an extract the data from there.

philcart SSC-Forever Points: 47795 More actions · Answer 4

Take a look at my script over here,

http://www.sqlserver.com.au/resources/ViewResource.aspx?resourceId=8

--------------------
Colt 45 - the original point and click interface

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

what will this select statement do?

It returns 1 = TRUE. So sysprocesses is a fake table. You can read about the OBJECTPROPERTY thing in BOL. It comes in handy in many situations.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ms CRB Mr or Mrs. 500 Points: 528 More actions · Answer 6

I tried the script capture_spwhoScript. I added my database to the script, I tried creating my server as a linked server and I received an error. You cannot create a local sql server as a linked server. Am I doing something wrong?

philcart SSC-Forever Points: 47795 More actions · Answer 7

If you already have the server setup as a Remote Server then you'll need to give the linked server a different name.

EG If you have a server called ServerA. It is most likely setup as a remote server called ServerA. so you'll need to give it a different name as a linked server.

sp_addlinkedserver 'ServerA2', '', 'SQLOLEDB', 'ServerA'

--------------------
Colt 45 - the original point and click interface

Ms CRB Mr or Mrs. 500 Points: 528 More actions · Answer 8

Hi

I got over the linked server error. server name stageserver, linked server stageserver1, database name production. I replaced the 3 database names in the script to production and the server name in the script to my linked server name of stageserver1. Is there anything else in the script that I need to modify? When the job runs it gets the error listed below.

Executed as user: AD-MEA\SQLSRVC. OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks for all of your help

MS_CRB

sa24 SSCrazy Eights Points: 8027 More actions · Answer 9

sa24

SSCrazy Eights

Points: 8027

February 16, 2005 at 8:10 am

#542542

Make sa to be the owner of the job

Ms CRB Mr or Mrs. 500 Points: 528 More actions · Answer 10

Ms CRB

Mr or Mrs. 500

Points: 528

February 16, 2005 at 8:12 am

#542544

sa is the owner.

Ms CRB Mr or Mrs. 500 Points: 528 More actions · Answer 11

Ms CRB

Mr or Mrs. 500

Points: 528

February 16, 2005 at 8:22 am

#542549

sa is already listed as the owner.

Ms CRB Mr or Mrs. 500 Points: 528 More actions · Answer 12

Phillip,

When I executer EXEC dbo.usp_CaptureActivity from the query analyzer it works fine. When I run it as a job with sa as the owner I get an error.

Question2

I was looking at the rolls in the tblActivitylog and it can become huge over time. What the client wants is to be able to run a query and only see the last time the login user accessed the system. They have 255 users and only want the login listed once. If the user logs in for the first time insert the row, after their login is already in the system they only want to update the lastUpdate field with the new date/time. Really they only care about the login and lastUpdate field, they don't care what they were doing. How do I accomplish this with a trigger on the tblActivitylog table?

Thanks again,

MS_CRB