SQL Server Agent Roles


We are evaluating using BMC’s Control-M, our corporate IT Job Management tool.

And, wanted to see what we will have to do to get it to work against SQL Server Instances.


SQL Server Agent Roles

In SQL Server, jobs are managed through SQL Server Agent.

PreDefined Roles

SQL Server Agent has predefined security roles.

SQL Server Management Studio ( SSMS )

Jobs are saved in the system database, msdb.

To view the roles, please do the following:

  1. Launch SQL Server Management Studio (SSMS)
    • Connect to the SQL Server Instance
    • Choose System Databases
    • From the list of System Databases, choose msdb
    • Within the msdb database, transverse to Security \ Roles \ Database Roles
    • The SQL Server Agent roles are noted having names that start with SQLAgent



Permission Set

Let us dig deeper into these roles and see what they afford us, where they are different, and what is the minimum we can get away with.


The roles are listed in increasing order of privileges assigned.

More precisely as Microsoft would say it, they are concentric.

Looked up the term concentric and here is how it is defined:

They are of or denoting circles, arcs, or other shapes that share the same center, the larger often completely surrounding the smaller.

And, so we can see that MSFT’s documentation is very useful, and here it is in verbatim:

The SQL Server Agent database role permissions are concentric in relation to one another — more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). 


Role Definition Details
SQLAgentUserRole Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.  a) Have permission on owned jobs
SQLAgentReaderRole SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. a) List all jobs – their properties, schedules, and executionhistory
SQLAgentOperatorRole SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. a) Manage ( enable or disable jobs, edit job steps )

b) They can execute, stop, or start jobs

c) Delete job execution history


Which Role?

For system jobs we do not want an external job manager as SQL Server Agent is able to do so sufficiently.

We only want an external job manager for specific jobs.

Let us review the predefined system roles and judge their appropriateness for what we have in mind:

  1. SQLAgentOperatorRole
    • Affords all roles to all jobs
    • Too much for us
  2. SQLAgentReaderRole
    • (+)
      • Able to create and and manage own job
      • Read privileges on all jobs; their steps, schedule, and run history
    • (-)
      • Job Management does not need to view job data nor review job run history
        • A bit much for our targeted need
  3. SQLAgentUserRole
    • Requirements
      • (+)
        • Create own jobs
        • Run owned jobs
          • Existing jobs ownership can be re-assigned
      • (-)
        • Job has be owned
          • We have to review what is the ramification of changing job ownership for each specific job


Follow Up

Our follow-up task is to review the impact of changing job ownership for specific jobs.



  1. Microsoft
    • SQL Server Agent Fixed Database Roles
    • Implement SQL Server Agent Security


General Vincent K. Brooks


In an age where voices can be reduced to soundbites, cliques and inflammatory comments and categorization.

In general finding the worst in each other and blanket statements about “what we have seen before“.

And, those buckets are based on National Origin and Religion.

How do we go forward?

And, so we ask ourselves how do we go forward or are we are just in a maze of bad choices, which leads to stillness, and assumption of a fetal position.

As I was watching an interview yesterday, I heard the name of Vincent Brooks.  I googled on his name and found a couple of freely and broadly accessible videos on youtube.



  1. General Brooks discusses his biggest challenges and biggest successes in Iraq.
    Uploaded On :- 2011-May-4th
  2. GEN Brooks message
    US Pacific, 4 Star General
    Uploaded On :- 2013-July-22nd
  3. LTG Brooks West Point Visit.mov
    Lt. Gen. Vincent K. Brooks, Commander of 3rd Army, returns to his Alma Mater to speak to the Corps of Cadets about Army Leadership.
    Uploaded On :- 2012-April-10th




GEN Brooks message, US Asia Pacific

  1. Command Video for Team 6
  2. Team Qualities
    • True test of a team is not missing a beat even as we change command
  3. Truly blessed to return the Four Star general to Asia Pacific since 1974
  4. Media
    • Another channel for me to air directly
    • It is not substitute to see and hear in person
  5. Opportunity
    • Training
      • Training our own and our partners and friends
    • Professionalism
      • Exporting professionalism
      • Your Professionalism will be available to our partners
        • Qualities
          • Be yourself
          • And, give each task your best effort
  6. Challenges
    • Fiscal Challenges
      • Fiscal challenges we have has a nation
        • Every dollar we are given, we have to stretch
        • Take care of our people and realize that we are fortunate to have the ones we have
    • Changing Culture
      • We can not allow practices that undermine our pride and the pride we feel as an Army
      • Eliminate Sexual Harassment and Sexual Assault
        • Actions that leave trauma in unit and members of our team
        • Have a culture where this experiences are not able to occur
        • To do
          • Start with yourself
          • Allow others to make it go away
          • Set example for others to see
        • zero tolerance
        • Trust
    • Gratitude
      • Thanks for welcoming my wife and I


Webprofusion Ltd – Certify The Web – Day 1


Security is being in the news a lot lately.

In this post, we will talk about using SSL, specifically reaping SSL certificates from LetsEncrypt.Org via “WebProfusion Ltd – Certify the Web“.


Client Options

Here are the Client Options available for Windows


WebProfusion Ltd – Certify GUI –
(.Net, WinForms )

In this post, we will go with WebProfusion Ltd – Certify the Web.




  1. Network
    • DNS
  2. Website Availability
    • Website Availability Test
  3.  Software
    • Microsoft .Net v4.5
  4. Microsoft IIS
    • Bindings



DNS Requirement

From a networking standpoint, the LetsEncrypt validation servers have to able to connect to the originating computer.

That rules out the following:

  1. Servers that are not reachable over the Internet
    • Servers that only have local IP Addresses


DNS Server Names

Here are a couple of popular DNS Servers:

Vendor Link DNS-1 DNS-2
Verisign  Link
Google  Link
OpenDNS  Link


DNS Validation

On MS Windows, we can use nslookup to validate.


Here is the syntax

nslookup [FQDN] [dns-server]

Sample – DNS – Google ( & )

nslookup web.labDomain.org 


Sample – Verisign ( & )

nslookup web.labDomain.org 



Website Availability

Website Availability Test

Here are some availability tools:

  1. Uptrends



Go to https://www.uptrends.com/tools/uptime.

Intentionally entered an invalid URL, in this case upTimeTest.cnn.com

Uptrends.com – Sample


We entered a FQDN that we know is not available.


We entered hyattHouse.com and we are able to successfully validate.



Microsoft .Net Framework v4.5

Although the software can be installed without first installing .Net v4.5, it can not be used.

If one tries to do so, the user is prompted to install .Net 4.5.

BTW, .Net v4.5 has its own requirement in terms of minimal OS.  And, those are:

  1. Windows 2003
    • .Net v4.5 can not be installed on MS Windows 2003
  2. Windows 7
  3. Windows 2012


Microsoft IIS

IIS – Site Bindings

Internet Information Server ( IIS )

Site Bindings

We can use IIS Manager and access the Site Bindings

Site Bindings – Original


Site Bindings – Add Binding

Click on the “Add..” button.

Add each hostname or alias that you will like to generate certificate for.

Please add only http entries.

The https will be added for you.


Site Bindings – After adding

In the screen above, we have added the hostname that we will like exposed.



Downloaded “Certify The Web” from the Vendor’s website.

As of 2017-July-15th, the current version is V2.0.7-beta4.



License Agreement



Select Destination Location



  1. 9 MB


Select Start Menu Folder



Ready to Install


Installing ….


Complete the Wizard




Launch “Certify the web“.

Initial Screen

Empty Canvas

New Certificate

Click the “New Certificate” button.

Managed Sites – New Certificate – Options



  1. Select IIS Site
    • Chose the IIS Site
  2. Name
    • The Name is only figurative
  3. Primary Domain Name
    • Please choose the Domain Name
    • If none shown, please visit the TroubleShooting section
  4. Alternative Domain Subject Name
    • All of the hostnames registered in the Site Bindings are listed


Managed Sites – New Certificate – Advanced




  1. Auto create/update IIS bindings ( use SNI )
    • Chose to use SNI
      • Please read more about SNI ( Server Name Indication )
      • As always Wikipedia is a good source and here is the Link


Once you are comfortable with your choices, please click the Save button.


Request Certificate

Here are the steps for actually requesting a certificate.

Saved Certificate Request

Here is the screen once a Certificate is Requested.



Certificate Received and Installed



  1. In the image above, our request has been validated, a certificate has been issued, and installed on our machine.



Primary Domain Name

Primary Domain Name – Empty

In the example that follows we just installed the Application and we are trying to add a “New Certificate”.

New Certificate

Error – “A primary domain must be selected”


  1. The error message states “A Primary Domain” must be selected
    • The reason is because we have not selected “Primary Domain Name



To fix, please …

  1. Launch IIS Manager
  2. Access Site
  3. Under Sites, select the Web Site
  4. In the Action Panel
    • Under Edit Site, Choose Bindings…
  5. In the “Site Bindings” window
    • Review listed Site Bindings
    • If not listed, click the “Add” button
      • The “Site Binding” window appears
        • In the Host name text box, add the host’s “Fully Qualified Domain Name



If you are running at a minimum MS Windows 7 ( desktop)  or 2012 ( server ), you should consider “Certify The Web“.

There is a lot more as this is only Day ONE.



  1. Certify The Web
    • Home Page
    • Docs
    • Getting Started
    • Issues
      • Issues – does not give list of possible domains #83
  2. Server Name Indication
  3. Browser – SSL
    • Google Chrome
      • Akemi Iwaya
        • Akemi Iwaya – How Do You View SSL Certificate Details in Google Chrome?
  4. DNS Servers – Public
    • Lifewire
      • LifeWire – Free & Public DNS Servers

Windows – Reviewing Services Start and Stop Times via Event Viewer & Log Parser Studio


Received an Incident this morning stating that out Power BI Gateway Service was done.

Spent a bit of time trying to see what brought the service down.


   Service Start and Stop Time
           , TO_STRING(TimeGenerated, 'yyyy-MMM-dd hh:mm tt') as [Timestamp] 
           , Message as [MessageLogged]
           , Strings as [StringsRaw] 
           , SUBSTR(Strings, 0, LAST_INDEX_OF(Strings,'|'))  as [ServiceName] 
           , SUBSTR(Strings, ADD(LAST_INDEX_OF(Strings,'|'), 1) , Strlen(Strings)) as [ServiceStatus] 


          ( EventID = 7036 )
                 ( Message like '%SQL%' )
              or ( Message like '%gateway%' )


      TimeGenerated desc


SQL Server (v2016) – Installation – Blocked – “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase”


In the process of installing “SQL Server 2016” for a new client.

As it is a new client and not really sure of which exact features they will be using, chose to install “All features“.

Install Blocked

Installation is blocked with the error pasted below:


Feature Rules



Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase.

Rule Check Result



This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed.  The Oracle Java SE runtime environment is software provided by a third-party software.

To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.




Visited Microsoft URL

Visited the listed Microsoft page and directed right away to Oracle’s Java download page.


Java Standard Edition Downloads

Here are the downloads available for java Standard Edition ( Java SE)



  1. JDK
    • We need we need JRE ( Java Runtime Engine), and not JDK ( Java Development Kit )
  2. Server JRE
    • Thought we could get away with the the lighter footprint Server JRE
  3. JRE
    • JRE


Server JRE

Server JRE – Downloads

Chose Windows x64.

Install Package Contents

The installer is bundled as a tar.gz.

Using 7-Zip extracted the file, but no installer.

Just app executable and class files.

Need more than that to install JRE and have SQL recognize that Java JRE is installed.



Java SE Runtime Environment 8 Downloads

Needed an executable offline installer and our bitness is 64 bit and so chose jre-8u131-windows-x64.exe.



Actual Installation is straightforward.




Destination Folder

Create destination Folder beforehand.

Here is a sample script.

@echo off
rem _folder=C:\Program Files\java\jre1.8.0_131
set "_folder=E:\ProgramFiles\Java\JRE1.8.0_131"

if not exist %_folder% mkdir %_folder%




Destination Folder ( Changed )


Java Setup – Progress


Java Setup – Complete


If you will be evaluating \ using Polybase as part of SQL Server v2016, please download and install Oracle JRE ( Java Runtime engine) beforehand.

For JRE, there are two packages available for download.  One is marked Server and the generic one.

Please choose the generic, full version.

SQL Server Agent Jobs and extraneous contents in logged messages


This morning I needed to dig into a SQL Server Job that was failing, but unfortunately the error was not being exposed\surfaced.



Message – Image

Message – Text

Executed as user: SQLADMIN. Jul 11 2017 12:19PM – Transaction Begin …. [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Transaction Began [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Preparing adding records into [UserAcct].[UserActivity] … [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Adding records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Update records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committing Transaction [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committed Transaction [SQLSTATE 01000] (Message 0). The step succeeded.



  1. If we take a deep look into the messages pasted above, we will notice multiple occurrence of “[SQLSTATE 01000] (Message 0)



Googled and found a very helpful comment from Tibor Karaszi.

Tibor Karaszi

How do I suppress [SQLSTATE 01000] messages in sp output?




SQL Profiler

Ran SQL Server Profiler and here is a screenshot:




  1. SQLAgent – Generic Refresher
  2. SQLAgent – Job Manager
  3. SQLAgent – TSQL JobStep ( Job 0x####…. : Step 1)
    • — network protocol: LPC
      set quoted_identifier on
    • set quoted_identifier off
    • select @@microsoftversion
    • select convert(sysname, serverproperty(N’servername’))
    • SET TEXTSIZE 1024
    • print ‘hello’




  1. Application Name :- SQLAgent – TSQL jobstep ( Job 0x )
  2. NTUserName :- SQLServerAgent
  3. LoginName :- NT Service\SQLServerAgent
  4. Client Process ID :- 2636


Task Manager

Reviewed “Task Manager” and confirmed that the process name for process ID is SQLAgent.exe


SQL Server Agent Step

Current Job Step Configuration

Revised Job Step Configuration


  1. Type
    • Original :- Transact-SQL Script (T-SQL)
    • Revised :- Operating System (CmdExec)
  2. Command
    • Original :- exec [csdemo].Products.UpdateCertificatesUserDataAutoConfirm
    • Revised :- sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d demo -Q “exec [csdemo].[Products].[UpdateCertificatesUserDataAutoConfirm]” -b


We changed from the internal Transact SQL Script tooling built into SQL Server Agent unto sqlcmd.exe

Sqlcmd exposes a lot more functionality.



Logged Messages

Logged Messages – Original

Logged Messages – Revised



Can’t go anywhere but to Tibor Karaszi, SQL Server MVP.

Like Eli Manning an MVP.