SQL Agent security
Scheduled jobs are common in most production data processing sites. Typically, these jobs are created by developers and DBAs. While members of the sysadmin server role used to be the only ones who could create and alter jobs, this is no longer the case from version 2005.
Three new roles have been added to the msdb database: SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole. Members of these database roles in msdb database have varying levels of access to the job system and can create or modify jobs and schedules. In your security audit, you may want to find out the members of these roles.
If you do not see any user accounts listed under these roles, you can to check the owners of the jobs defined in the SQL Server.
SELECT a.name AS JobName,
b.name AS OwnerName
FROM msdb.dbo.sysjobs a
INNER JOIN sys.server_principals b
ON a.owner_sid = b.sid
Publication access list
If the database server is participating in replication as a publisher, it may be worthwhile to see who has access to its publications. The Publication Access List or PAL is a list of accounts that can connect to a publication and create/synchronise subscriptions against it.
Most of the accounts listed under the PAL should be system or service related. You may want to take note if you see Active Directory user accounts listed here.
Endpoints
The introduction of endpoints was one of the significant upgrades of SQL Server 2005 from its predecessors. Endpoints are like electric wall outlets or telephone cable ports for SQL Server. It is a connection mechanism used by a number of built-in features and technologies such as service broker or database mirroring.
A SQL Server endpoint is basically a port that listens for a particular type of traffic that uses a particular type of protocol. At present, the only two types of protocol supported are TCP and HTTP. The endpoint also will recognise only a definite type of traffic or “payload”. The payload for the incoming connection can constitute raw T-SQL commands, SOAP messages (for web service applications), service broker or database mirroring traffic. Some of the endpoints are already present by default – like the ones used by Dedicated Administrator Connection or T-SQL commands. Other endpoints – like the ones used for database mirroring – need to be created explicitly.
Endpoints are defined at a server level and like any other server level objects they can be granted permission to. Server level principals can be explicitly granted privilege to connect, take ownership or control endpoints.
If you are curious about non-default endpoints present in the SQL Server and any explicitly granted permission on them, you can run the following query:
SELECT a.name AS Endpoint_Name,
a.type_desc AS Endpoint_Type,
b.name AS EndPoint_Owner,
a.protocol_desc AS Endpoint_Protocol,
c.permission_name AS Endpoint_Permission,
c.state_desc AS Permission_Status,
d.name AS Permission_Granted_To
FROM sys.endpoints a
INNER JOIN sys.server_principals b
ON a.principal_id = b.principal_id
INNER JOIN sys.server_permissions c
ON a.endpoint_id = c.major_id
INNER JOIN sys.server_principals d
ON c.grantee_principal_id = d.principal_id
WHERE a.is_admin_endpoint = 0
AND a.type_desc <> ‘TSQL’
]]>