PBM: script warning

Question

PBM: script warning

deubel_m

SSC-Addicted

Points: 451
More actions
March 25, 2019 at 12:19 am

#380186

Hello community,
I want to use some policies on my sql servers. I have created them and everything works fine except I get this Warining "The policy: '<policy name>' contains scripts. You should only run policies from a trustworthy source.".
The policy contains scripts. So far so good. But how do I get rid of this warning?
It seems that the policies containing scripts and generate this warning are not able to be executed by the sql server agent. I always get the error "The server principal "##MS_PolicyTsqlExecutionLogin##" is not able to access the database "AdventureWorks2012" under the current security context.".
I don't get this error when my other policies run.
Any help is appreciated!
Regards
Marcus

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 1

Does the SQL Server Agent account have CONNECT permission to the database in question?

See this link - https://social.msdn.microsoft.com/Forums/en-US/7c24e908-d900-484a-80fe-08df018ac96c/policy-management-best-practices-database-page-status-scheduled-evaluation-security-issue

deubel_m SSC-Addicted Points: 451 More actions · Answer 2

HandyD - Monday, March 25, 2019 1:02 AM
Does the SQL Server Agent account have CONNECT permission to the database in question?
See this link - https://social.msdn.microsoft.com/Forums/en-US/7c24e908-d900-484a-80fe-08df018ac96c/policy-management-best-practices-database-page-status-scheduled-evaluation-security-issue

Yes, the sql server agent account has the sysadmin role.

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 3

What about the ##MS_PolicyTsqlExecutionLogin## login?

As per this link, that login needs sufficient READ access to each database in the policy in line with the T-SQL script used in the policy to evaluate the policy. You need to check the scripts you're using and determine what READ access is required in each database for that login. Alternatively, you might get away with just adding that login to the database role db_datareader on each database being checked.

deubel_m SSC-Addicted Points: 451 More actions · Answer 4

HandyD - Monday, March 25, 2019 5:37 PM
What about the ##MS_PolicyTsqlExecutionLogin## login?
As per this link, that login needs sufficient READ access to each database in the policy in line with the T-SQL script used in the policy to evaluate the policy. You need to check the scripts you're using and determine what READ access is required in each database for that login. Alternatively, you might get away with just adding that login to the database role db_datareader on each database being checked.

Thanks for this link. Did find it when I was searching. I fixed by creating a server role:
create server role dbReadonlyAccess; go grant view server state to dbReadonlyAccess; grant view any database to dbReadonlyAccess; grant connect any database to dbReadonlyAccess; grant select all user securables to dbReadonlyAccess; go