Improve company productivity with a Business Account.Sign Up

x
?
Solved

Question for vujos

Posted on 2000-04-07
2
Medium Priority
?
313 Views
Last Modified: 2012-05-04
Regarding triggers on system tables, my experience has been as simonsabin says.  I created it but it never fired regardless how i tried.
Please  provide me with the explanation how to create it and have it execute.
0
Comment
Question by:xoxomos
2 Comments
 
LVL 1

Accepted Solution

by:
vujos earned 200 total points
ID: 2697150
Hi,

Following is tested on MSSQL 6.5 (did not have time yet to try on 7.0).

But let's start :

1)<enable update on system tables>

sp_configure 'allow update', 1
reconfigure with override

2)<make syslogins table visible in EM>

update sysobjects set sysstat = 99 where name = 'syslogins' and id = 33

3)<add custom message>

sp_addmessage 50009, 16, 'This is my test message'

4)<create trigger for add, upd of login>

CREATE TRIGGER reminder ON syslogins
FOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)
GO

3)<make syslogins invisible in EM>

update sysobjects set sysstat = 113 where name = 'syslogins' and id = 33

4)<disable system tables updates>

sp_configure 'allow update', 0
reconfigure with override

5)<try to add new login from EM or run>

sp_addlogin 'test1', 'testing', 'master', default


Well there you go, you still think that this was easy ?

General comment is still walid : any playing with system tables should be avoided. If you have to, please backup all the databases (you could end up rebuilding your master db - you know what that is). Please do not try above on production system. This is only an demo.

Hope this helps.

0
 

Author Comment

by:xoxomos
ID: 2700889
O.K.  i'll try that and keep in mind not to do it on a production system ---- just yet.  I'm using 6.5 anyhow.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question