SQL Service account rights

• Animal Magic

  SSChampion

  Points: 13964

  More actions

  February 17, 2013 at 10:38 am

  #289663

  Hi,

  I'm having a little trouble fully understanding why a couple of sql agent jobs are not working correctly. I know it has something to do with how SQL now uses service SIDs and you no longer have to assign permissions directly to the database server as its all handled by the sql services.

  My sql agent uses a domain account, lets call it Mydomain\sqlagent. This account has no rights on the database server which can be verified by trying to login to SSMS, which fails. It does however run all the sql jobs etc without issue by using the NT SERVICE\SQLSERVERAGENT. However a couple of things fail and im not 100% sure why.

  One of which is a job which emails me deadlock info (using traceflag T1222 to write to the log). The job fails with the following error

  Executed as user: MyDomain\sqlagent. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

  Removing the email send code allows the job to run fine (its a standard send db mail with @query attach params), also giving MyDomain\sqlAgent sysadmin rights also allows the mail to be sent with the expected results.

  What bit is missing so that the service account has sysadmin rights via the SQLSERVERAGENT association but it still doesnt really have full rights?

  Thanks in advance, and if anything needs clarification please let me know.

  John

• Animal Magic

  SSChampion

  Points: 13964

  More actions

  February 17, 2013 at 12:43 pm

  #1588686

  For info, here is the full query with the bit in bold the section which causes the job to fail

  --== This is for SQL 2005 and higher. ==--

  --== We will create a temporary table to hold the error log detail. ==--

  --== Before we create the temporary table, we make sure it does not already exist. ==--

  IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null

  BEGIN

  DROP TABLE tempdb.dbo.ErrorLog

  END

  --== We have checked for the existence of the temporary table and dropped it if it was there. ==--

  --== Now, we can create the table called tempdb.dbo.ErrorLog ==--

  CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,

  logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))

  --== We create a 3 column table to hold the contents of the SQL Server Error log. ==--

  --== Then we insert the actual data from the Error log into our newly created table. ==--

  INSERT INTO tempdb.dbo.ErrorLog

  EXEC master.dbo.sp_readerrorlog

  BEGIN

  delete from tempdb.dbo.ErrorLog

  where Id < (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like '%deadlock-list%' order by Id DESC)

  --== Set a variable to get our instance name. ==--

  --== We do this so the email we receive makes more sense. ==--

  declare @servername nvarchar(150)

  set @servername = @@servername

  --== We set another variable to create a subject line for the email. ==--

  declare @mysubject nvarchar(200)

  set @mysubject = 'Deadlock event notification on server ' + @servername+ '.'

  --== Now we will prepare and send the email. Change the email address to suite your environment. ==--

  EXEC msdb.dbo.sp_send_dbmail @recipients='DL_DBA@paystream.co.uk',

  @subject = @mysubject,

  @body = 'Deadlock has occurred. View attachment to see the deadlock info'

  ,@query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog',

  @query_result_width = 600,

  @attach_query_result_as_file = 1

  --== Clean up our process by dropping our temporary table. ==--

  DROP TABLE tempdb.dbo.ErrorLog

  END

  Running it without the bold bit fires an email as expected, the job just fails when the @query bit is added back in.