Check whether xp_cmdshell is disabled or not

Xp_Cmdshell

- Spawns a Windows command shell and passes in a string for execution.

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed.

xp_cmdshell can be enabled and disabled by using the Policy-Based Management or by executing sp_configure

To check whether xp_cmdshell is disabled or not. Run the following Query.

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value

FROM  sys.configurations

WHERE  name = 'xp_cmdshell' ;

To enable the xp_cmdshell, you can refer the following msdn doc.

Reference : http://technet.microsoft.com/en-us/library/ms175046(v=sql.105).aspx

                 http://technet.microsoft.com/en-us/library/ms190693(v=sql.105).aspx

Enable SQL-Server Agent for non-admin User

Non-admin users will have public access to their databases, and usually SQL Server agent won't show in their Management studio. If you want the public user to create or manage or execute the Jobs in the SQL SERVER, you have to add 3 important SQL Agent fixed database roles to the msdb database.

-  SQLAgentUserRole
-  SQLAgentReaderRole
-  SQLAgentOperatorRole

How to add those roles

1. Navigate through Object Explorer -> Security -> Logins(select the user, then right click) -> Properties -> User Mapping -> Login Properties.

2. Select msdb database.

3. Select SQLAgentUserRole, SQLAgentReaderRole and  SQLAgentOperatorRole under Database role membership for : msdb.

That's it. You are good to go....