23

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.

3
  • 2
    You may want to add the version and edition of SQL Server you are using.
    – Hannah Vernon
    Oct 1 '12 at 6:00
  • 1
    I know this is an old post... But also remember, that if SQL Server Agent is disabled in Services... You also won't see it in management studio.
    – user45047
    Aug 5 '14 at 19:04
20

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
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

6
  • Actually I installed the SQL Server Enterprise Edition. I have not installed the SQL Server Express since I do not have the CD. So, how can I get the SQL Server Agent visible?
    – Jack
    Oct 1 '12 at 6:02
  • Could it be that I install SQL Server 2008 Enterprise on a Windows Server 2008 R2 cause such problem?
    – Jack
    Oct 1 '12 at 6:03
  • 1
    It should be automatically in SSMS, assuming it is installed. Did you choose to install SQL Server Agent during installation of SQL Server? Running on Windows Server 2008 R2 is not the problem.
    – Hannah Vernon
    Oct 1 '12 at 6:06
  • 2
    You also have to make sure that you are not using the Express version of SSMS, and that you have sysadmin rights to the instance, or the proper roles in the msdb database. Oct 1 '12 at 16:52
  • 1
    Um, PowerShell seems like a solution looking for a problem. This still works in SQL Server 2012. mssqltips.com/sqlservertip/1272/… Sorry, unrelated to this answer. Mar 26 '13 at 16:07
3

Thanks Boris Hristov and Max Vernon for enlighten me.

Yes, 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.

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.

1
  • 1
    is it possible to explain further on how you issue the commands directly in a query analyzer windows?
    – Jack
    Oct 4 '12 at 5:36
2

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.

0

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.

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.