0

I'm attempting to implement Policy Based Management on a SQL Server 2012 instance also acting as a Central Management Server using the Enterprise Policy Management Framework. The job running the Powershell script has a login on all servers allowing it to connect and view all databases. It also has all related msdb roles on the CMS. However, the policy only executes against a database if its guest user is enabled. Instances with no databases with an enabled guest just return "No Target Found".

Am I missing something obvious, or do I have to give the agent account a user on every database on every instance?

Improve this question
2
  • 1
    If I well understood what you said, the policy executes against databases, which justifies that it returns "No Target Found" on instances without databases.
    – JoseTeixeira
    Commented Aug 27, 2014 at 9:50
  • All of the instances have databases. It just doesn't see databases that don't have guest enabled, despite the fact that it has the "View All Databases" effective permission on every instance.
    – Neghtasro
    Commented Aug 27, 2014 at 14:22

1 Answer 1

Reset to default
1

I've spent most of today bashing my head against this, and didn't come up with anything. I tried:

  • Messing with msdb roles on the CMS
  • Messing with msdb roles on the target servers
  • Messing with server roles on the CMS and target servers
  • Petitioning the gods of old
  • Trying something convoluted involving credentials and proxies

In the end, nothing worked. I believe this is possible on SQL Server 2014, which has a "CONNECT ALL DATABASES" server-level permission, but 2012 has no equivalent that isn't also a much larger security risk than I'm willing to accept.

I decided to bite the bullet and add the account as a user on every database. Here's what I did:

exec sp_MSforeachdb 'if ''?'' in (''master'',''msdb'',''tempdb'') return; use [?]; CREATE USER [domain\account] FOR LOGIN [domain\account];'

Executing this script against the CMS added the user to every database on every instance except for master, msdb, and tempdb. I left model in so that when we create databases in the future, they'll already have the user added.

If anyone has a better way that doesn't involve mapping to every database, feel free to share, but at this point I'm not convinced it can be done (short of making the account sysadmin, which I doubt would make it into production).

Improve this answer
1
  • This isn't much of an answer ("I tried a bunch of stuff and the next version can do it so here's an easier way to dodge the question"), so I'm not accepting it. I just figured I'd document what ended up working for me here.
    – Neghtasro
    Commented Aug 27, 2014 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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