Triiger to execute a proc

Question

Post reply

Triiger to execute a proc

Minaz Amin

SSChampion

Points: 11052
More actions
July 20, 2015 at 1:47 am

#305886

I wanted to execute a proc whenever a new login or a login is droped is created on the server. I was trying for CREATE_LOGIN in the following code but not able to capture the loginname and pass it to the proc.
Please help, I am a not good coder.
Server object Trigger:-
USE [master]
GO
/****** Object: DdlTrigger [LoginCreateTrigger] Script Date: 7/20/2015 3:41:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [LoginCreateTrigger] ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
DECLARE @data XML;
DECLARE @login varchar(max);
SET @data = EVENTDATA();
SET @login = CAST(@data AS VARCHAR(max));
EXECUTE testme.[dbo].[testlogin] @login
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [LoginCreateTrigger] ON ALL SERVER
GO
USE [testme]
GO
/****** Object: StoredProcedure [dbo].[testlogin] Script Date: 7/20/2015 3:45:16 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[testlogin] @login varchar(max)
AS
SELECT *
FROM dbo.emp
WHERE login = @login
go
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
"More Green More Oxygen !! Plant a tree today"

Viewing 4 posts - 1 through 3 (of 3 total)

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

Emil B SSCertifiable Points: 5568 More actions · Answer 1

Have a look here

http://sqlandme.com/2011/07/13/sql-server-login-auditing-using-logon-triggers/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

To start with, unless you're using several thousands of characters of XML for login names, this piece isn't going to work correctly.

SET @data = EVENTDATA();

SET @login = CAST(@data AS VARCHAR(max));

EXECUTE testme.[dbo].[testlogin] @login

What EVENTDATA returns is not a login name, it's a large piece of XML containing, among other things, the command executed. You'll need to shred the XML, extract the login name and pass just the login name to your procedure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

Thanks Gail Shaw, I was able to capture login and passed it to Sproc.

"More Green More Oxygen !! Plant a tree today"