I have a question which is a 100% duplicate of msg through sql server xp_cmdshell and I nevertheless have to ask this question again because the old question has no answer. And I cannot even add a comment to the old question because this requires reputation of 50.
My problem is: I need to send messages to my network users from within a trigger on a table in SQL Server 2014. This way I hope to find out who or what is messing up with the data in this table, probably by means of some bug planted somewhere on the network. I used to successfully use this method in the times of Windows 2000 and SQL Server 2000, and would like to use it now because it provides good results.
Unfortunately, I encounter the same problem as the author of the old question: my statement in my trigger
EXEC master..xp_cmdshell 'msg * Table is changed'
I get this error
- does not exist or is disconnected
While from Windows command line the command 'msg * Table is changed' works perfectly: every session on my computer immediately receives the message
I tried the suggested in the old question method of using a dedicated proxy account for xp_cmdshell but my command
EXEC sp_xp_cmdshell_proxy_account 'IdeaPad\ssa', 'pas864214'; GO
produces the following error:
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: 5(Access denied.), Error Status: 0.
Can anybody advise me how to overcome this obstacle? I issue my command under the sa user, who has all access to SQL Server. Why at all may he be denied access?
Or could you suggest another method of solving the problem? All other advice from the old question, including PowerShell or SQLCMD through SQL Agent, or DBMail - are ruled out because they do not provide IMMEDIATE messaging, which is essential for identifying the bug source
Many thanks everybody for your support. Finally I found the best way, based on the idea of @AlwaysLearning: temporarily change the account under which SQL Server is running. Change to the account of domain admin, or local admin in a domain-less environment. This way the msg.exe command is executing without error, I tested it.
My trigger code to catch the bug will be as follows:
CREATE TRIGGER [monitor_changes] ON [dbo].[ParusNetUser]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @tmpstr as VarChar(255)
SET @tmpstr = 'msg * /SERVER:IdeaPad Table is changed from computer ' + HOST_NAME()
EXEC master..xp_cmdshell @tmpstr, no_output
This way I will catch the bug in ten minutes at most. After that, I will reset the account under which SQL Server is running, as described here: https://learn.microsoft.com/en-us/answers/questions/523517/how-to-reset-the-logon-for-the-sql-server-services That’s it, done.