2013/05/09

SQL Agent Log Error The process could not be created for step 1 of job reason 1314

今天發現一個錯誤訊息

The process could not be created for step 1 of job reason 1314

 

尋線追查後發現,若 SQL Agent 帳號有變更,需加入群組 SQLServer2005SQLAgentUser$<machine_name>,再重啟 SQL Agent 服務後排除問題

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/f5201165-d675-46a7-abc7-24c92adb362b/

1314 = "A required privilege is not held by the client."
It is the SQL Agent account with the trouble perhaps, and not just the proxy account.
Make sure you reset the SQL Server service account by using the Configuration Manager, so that the service account you choose gets added into SQLServer2005SQLAgentUser$<machine_name>. If the current service account for SQL Agent is not in that group, add it to that group manually, or swap the service account to local system, save, then swap it back again to the desired account and type the password (using configuration manager)
You may have missed a couple of the user rights (administrators group may not include these privileges)
http://msdn.microsoft.com/en-us/library/ms143504(SQL.90).aspx
- Log on as a service (SeServiceLogonRight)
- Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
- Log on as a batch job (SeBatchLogonRight)

- Replace a process-level token (SeAssignPrimaryTokenPrivilege)
- Bypass traverse checking (SeChangeNotifyPrivilege)
- Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

2013/05/07

KB-The EXECUTE permission was denied on the object usp_Michael, database dbMichael, schema 'dbo'

若帳號只屬於 db_reader,不是屬於 db_owner 角色時,語法中含有 store procedure or function 時,會出現此類錯誤訊息

The EXECUTE permission was denied on the object ????, database ????, schema 'dbo'

 

解決方式:

1.建立角色

CREATE ROLE [myRole] AUTHORIZATION [dbo]

2.設定執行預存程序或函式權限給與角色

GRANT EXEC ON dbo.usp_Michael TO myRole

 

3.將登入帳號加入角色

EXEC sp_addrolemember N'myRole', N'login_user'