24

I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8

  1. Now to schedule your newly created script. In SSMS connect to the server you want to trace on. Go to your SQL Server Agent and expand it so you can see the Jobs folder. Right-click the Jobs folder and select New Job.

SQL Server Agent -> Jobs -> New Job

I cannot not find SQL Server Agent. Where can I make this SQL Server Agent visible? I have given the current user all the access permission.

Stevoisiak
  • 111
  • 6
Jack
  • 2,557
  • 14
  • 37
  • 42

7 Answers7

22

If you are not granted the needed permissions, you will not be able to see the Agent, no matter whether you run Enterprise/Standard/Datacenter, etc...

The roles needed are sysadmin, or one of the individual roles listed in the article SQL Server Agent Fixed Database Roles (Microsoft | SQL Docs)

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

Also, if the SQL Server Agent service is disabled or not running, you also won't see it in SQL Server Management Studio.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Boris Hristov
  • 331
  • 1
  • 3
11

SQL Server Agent is only available with certain versions of SQL Server. For instance, it is not available at all with SQL Server Express.

If you have SQL Server Standard or Enterprise it will be available in SQL Server Management Studio (SSMS) at the bottom of the listing of server items.

enter image description here

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
4

I know it's old and already resolved but I encountered the issue and come across this page so maybe it will help someone.

In some cases your SQL Server Agent is not expandable with a label (Agent XPs disabled). In this case run this code to enable it.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Agent XPs', 1;  
GO  
RECONFIGURE  
GO

Here's the information related to this action.

Baz Guvenkaya
  • 291
  • 3
  • 5
4

In order to see the MSSQLSERVER, the user need to have the sysadmin permission. Also, if the MSSQLSERVER is the default instance for the SQL Server 2008, one will just need to call upon the Hostname and they will be able to see the SQL Server Agent.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Jack
  • 2,557
  • 14
  • 37
  • 42
2

You don't need SQL Server agent to start a server side trace. That is just to schedule it.

You can issue the commands directly in a management studio / query analyzer window.

Here is how:

Open SQL Server Profiler and setup the trace that you are interested in. Run the trace, then stop it. Go to File-->Export-->Trace Definition and select the target you are interested in.

This will save the SQL commands needed to start the trace in SQL Management Studio.

I use this technique all the time.

datagod
  • 7,141
  • 4
  • 38
  • 58
1

Not seeing the SQL Server Agent icon in SSMS' Object Explorer tree on a non-express edition SQL server where you are not a member of sysadmin, but are a member of at least one of the SQL Server Agent Fixed Database Roles can also be because you login using a windows login that is allowed in through a windows group only: no entry exists in master.sys.server_principals for your windows login.

The reason for this is that IS_ROLEMEMBER() always returns 0 when the group membership is via a windows domain group instead of the domain login itself. This is documented in the link above as:

IS_ROLEMEMBER always returns 0 when a Windows group is used as the database principal argument, and this Windows group is a member of another Windows group which is, in turn, a member of the specified database role.

Or it may be also be caused by the SQL server not being able to contact the AD to retrieve the required information. From the same link:

If the optional database_principal parameter is not provided and if the database_principal is based on a Windows domain login, it may be a member of a database role through membership in a Windows group. To resolve such indirect memberships, IS_ROLEMEMBER requests Windows group membership information from the domain controller. If the domain controller is inaccessible or does not respond, IS_ROLEMEMBER returns role membership information by accounting for the user and its local groups only. If the user specified is not the current user, the value returned by IS_ROLEMEMBER might differ from the authenticator's (such as Active Directory) last data update to SQL Server.

Please don't down-vote this: Even though this answer does not result in a way to make the SQL Server Agent icon show itself in the object explorer, it still provides an answer to why it is not shown in some circumstances.

The way to make the icon show for this particular user would be to explicitly add the windows user as a login and add a user in the msdb, which is a member of one of the SQL Server Agent Fixed Database Roles. However this often defeats the purpose why you used those groups in the first place.

rrozema
  • 111
  • 3
-2

Go to connection properties -- type msdb instead of default under options > connection properties > Connect to Database > msdb -- then connect

Arr
  • 1