Wednesday, June 21, 2017

Using xSQL Profiler with a limited access SQL user

The process of tracing events on a SQL Server and collecting those event data, is not among the usual tasks that a user performs on SQL Server. Because of this, the T-SQL commands that are executed to perform the start / stop trace and data collection operations, demand a very specific set of permissions from database users that are not system administrators. I've struggled a bit with this in the past which is why I'm writing this article to provide a step by step guide to making tracing with xSQL Profiler work with non-sysadmin users. To demonstrate these examples I've created a user called 'xsql_profiler_user'. Of course you can specify whatever name you like instead. Let's go over those requirements and how to fulfill them:

  • First of all, the SQL user needs to have access to the master database and all the databases that you want to trace. Giving the new user access to these databases is quite easy. on SSMS simply go to the [SQL Server name] > Security > Logins and double click on the name of your login. In this case, 'xsql_profiler_user'. Then go to the User Mapping section and select all the databases you want.
  • To be able to start / stop traces in a database, the xsql_profiler_user needs to have ALTER TRACE permissions on the server. To give these permissions to the user you again go to [SQL Server name] > Security > Logins > [name of the login], double click it, go to the 'Securables' section and grant Alter trace permissions from the 'Explicit' tab.
xSQL Profiler configures the traces to write their data into trace files which are then read, their data transferred to the repository and deleted. As you might have imagined, the proper permissions need to be in place to perform all these operations. 
  • xSQL Profiler uses SQL Server's xp_cmdshell to delete the files after it uses them, which means that the user you provide for the database connection (in this case xsql_profiler_user) needs to have EXECUTE permission on xp_cmdshell. To do this, simply run:
    GRANT EXECUTE ON xp_cmdshell TO xsql_profiler_user
  • Even though we granted EXECUTE permissions on xp_cmdshell we're still not done. Because the user is not system admin, SQL Server will not allow it to execute xp_cmdshell with the SQL Server's windows account, so we need to create a new windows account to use as a proxy for the xp_cmdshell. Go to Computer Management > Local Users and Groups > Users and create a new user. I've named mine 'xp_cmdshellproxy'.
  • After this, you need to give this user the 'Log on as batch job' right. This is because SQL Server will try to use this user to run batch commands without actually logging on with it, and windows will not allow it unless it has the Log on as batch jog right. To do this, search for 'Local Security Policy', go to Local Policies > User rights assignment, double-click on 'Log on as batch job' and add the user created in the previous step.
  • Next, assign full rights for the user in the Load temp trace data folder.
  • The last thing we need to do is configure SQL Server to use the windows account we just created as a proxy account for xp_cmdshell. To do that, run this query:
    EXEC sp_xp_cmdshell_proxy_account 'domain\acountname','password'
After you have completed all the above steps, you should be good to go.

0 comments:

Post a Comment