Elevate permission on a SQL login having read access to run the SQL Profiler

Sometime it’s necessary to allow SQL logins only to have read access to the respective SQL Servers. But it becomes a necessity to allow the developers to run the SQL Profiler when it comes to troubleshoot or to debug a flow and to find the exact point where the problem is generating. How ever it’s a very common opinion that a login having a read only access cannot run the profiler unless it’s included into the ‘sysadmin’ SQL role.
Well this isn’t entirely true. We could allow a SQL login to start and monitor SQL activity using the SQL Profiler and yet keeping the read only access to the SQL schemas. Follow these steps to achieve this:
1. Connect to the SQL Server and expand the Security->Logins node



2. Right click the desired login and go to ‘Properties’ from the context menu



3. Go to the ‘Securables’ tab and check/tick the Grant checkbox on ‘Alter Trace’. And click ok to save the details.



Now you can run the profiler and monitor details using that SQL Login.

Error occurs when CTE Alias is ‘RESULT’ - Incorrect syntax near 'RESULT'

Few days back one of my colleague ran into a very strange issue when he was asked to do an alteration to an existing procedure. The procedure was having a SELECT statement which included few Common Table Expressions. It has been running without an issue, until we tried to save the alteration. The alteration was a pretty simple one which was just to add couple of more columns. The query was similar to the one shown below

;WITH RESULT AS(

    /* Your logic  */

    SELECT GETDATE() AS DTE

)

SELECT * FROM RESULT

How ever when we tried to save the changes, it was throwing the following error.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'RESULT'.

But when we go through the query we couldn’t find any issues, and it seems the syntax was quite accurate. So we tried a different server and was able to save the changes without any error. The only difference was one server was running SQL Server 2012 which is having a higher build

Works fine on the following build

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Throws an error on the following build

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

And when googled we could find that ‘RESULT’ is a future reserved keyword:

https://msdn.microsoft.com/en-us/library/ms189822.aspx

But we were still left out with a question of why it failed on an earlier build but succeeded on a most recent build. Please feel free to comment on this if you have further information.