Home » Profiler Trace
Category Archives: Profiler Trace
Restrict user to login from single Host
Restricting user to login from single host may be sometimes required from security point and other business requirements. We can achieve it through SERVER LEVEL LOGON TRIGGER.
ALTER TRIGGER TR_CHECK_LOGIN_TEST_HOST
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @HOSTNAME VARCHAR(48)
DECLARE @PROGNAME VARCHAR(100)
SELECT @HOSTNAME = HOST_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = @@SPID
IF ORIGINAL_LOGIN() = ‘TEST’ AND @HOSTNAME =’HostName’
ROLLBACK;
END
Once you create above trigger, Login “TEST” will be able to connect from specified host only. Connection from any other host will be failed and face below error: –
You can also find related errors in SQL Server Error log as well.
Error: 17892, Severity: 20, State: 1.
Logon failed for login ‘TEST’ due to trigger execution. [CLIENT: <local machine>]
Error: 3609, Severity: 16, State: 2.
The transaction ended in the trigger. The batch has been aborted.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Thanks to Toadworld.com team for recognition !
Thanks to Toadworld.com team for recognition !
Toadworld.com Profile : http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
2013 with You & SQL Server
The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.
Here’s an excerpt:
The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 41,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 15 sold-out performances for that many people to see it.
Text information from RPC trace events not displayed in Profiler
In SQL Server 2005 and later, the information in the TextData column captured for the RPC:Starting and RPC:Completed events is also available in the BinaryData column. For performance reasons, it is more efficient to include the BinaryData column and omit the TextData column, when adding these events to the trace. If the BinaryData column is included, Profiler will extract the text from that column and display it in the lower event details pane, even if the TextData column was not included for the event.
However, a problem occurs when the trace does not contain ANY events with the TextData column. If such a trace is open in Profiler, the TextData column is not displayed in the grid, and the text information associated with the RPC:Starting and RPC:Completed events is not displayed in the lower event detail pane, even though the information is available in the BinaryData column.