Is it Possible to create trigger on system view?

CK 81

We want to create trigger for master database system view (sys.server_principals). Is it possible?

Basically I want to create a trigger when a login gets created/updated/modified.

Please let me know whether its possible and let me know how to achieve?

Mike Bowen 1,781 Reputation points Microsoft Employee

2023-03-21T20:11:44.37+00:00

Hi @CK ,

The [SQL Server Open Specifications] tag is dedicated to supporting issues related to open specifications. You can find open specifications here https://learn.microsoft.com/en-us/openspecs/

Since your question does not pertain to one of these specifications, I've removed the [SQL Server Open Specifications] tag. If I've misunderstood your question and you believe this is related to the Open Specifications documents, please clarify, providing the document name and section with which you have the question or issue.

Best Regards,

Michael Bowen

Escalation Engineer - Microsoft Open Specifications
PercyTang-MSFT 12,501 Reputation points Microsoft Vendor

2023-03-24T09:22:51.7766667+00:00

Hi @Karthikeyan Chinnusamy

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
PercyTang-MSFT 12,501 Reputation points Microsoft Vendor

2023-03-31T08:24:39.12+00:00

Hi @Karthikeyan Chinnusamy

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Accepted answer

Erland Sommarskog 111.1K Reputation points MVP

2023-03-21T21:59:28.68+00:00
No, you cannot create triggers on system tables.

However, you can create a server-level DDL trigger for the task. Here is a quick demo:

CREATE TRIGGER modify_login_tri ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN AS SELECT eventdata() go CREATE LOGIN testing WITH PASSWORD = 'NotSoSecret' ALTER LOGIN testing WITH PASSWORD = 'EvenLessSecret' DROP LOGIN testing go DROP TRIGGER modify_login_tri ON ALL SERVER

The eventdata function gives you details about the command that fired the trigger.
Please sign in to rate this answer.
CK 81 Reputation points

2023-04-26T12:05:09.39+00:00

Hi @Erland Sommarskog , Thankyou, Above script is working for create login and alter password, but when we update the system roles its not working. I tried below its not working, is there any other way?

CREATE TRIGGER ServerRole_Trigger ON ALL SERVER FOR ALTER_SERVER_ROLE AS EXEC msdb.dbo.sp_start_job N'NewJob' ; go

Erland Sommarskog 111.1K Reputation points MVP

2023-04-26T20:20:23.63+00:00

I found out by testing that ALTER_SERVER_ROLE fires when you rename the role.

Then I read https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events?view=sql-server-ver16 to learn that for trapping the adding of members, we need the the ADD_SERVER_ROLE_MEMBER event.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

PercyTang-MSFT 12,501 Reputation points Microsoft Vendor

2023-03-22T02:08:33.66+00:00

Hi @Karthikeyan Chinnusamy

You can create triggers on normal views. But creating triggers on system views doesn't seem very realistic.

You can try the method provided by Erland.

For more information about DDL triggers, you can refer to this official document.

https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16

Best regards,

Percy Tang
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Is it Possible to create trigger on system view?

1 additional answer

Your answer