Sql-server – Why do the backup policies fail when a database’s guest user is disabled

powershellsql serversql-server-2012

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?

Best Answer

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).

Related Solutions

Sql-server – SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints

I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine.

#SQL 2008 R2
#add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#SQL 2012
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$InstanceName = "myservername"

$query = @"
CREATE TABLE dbo.authors(
    au_id int NOT NULL,
    au_lname varchar(40) NOT NULL,
    au_fname varchar(20) NOT NULL

 CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED 
(
    au_id ASC
)
) 
"@

$InstanceObject = New-Object "Microsoft.SqlServer.Management.SMO.Server" "$InstanceName"

$db = $InstanceObject.Databases['tempdb']
$db.ExecuteNonQuery($query)

$MyScripter = New-Object "Microsoft.SqlServer.Management.SMO.Scripter"

$MyScripter.Server = $InstanceObject

$MyScripter.Options.DriAll = $true
#$MyScripter.Options.DriPrimaryKey = $false
#$MyScripter.Options.DriClustered = $false

$MyScripter.Script($InstanceObject.Databases['tempdb'].Tables['authors'])

Sql-server – SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Since you are using SQL Server 2008 R2 you can simply create your SQL Agent Job with the step configured as a "PowerShell" type, instead of trying to use the CmdExec. You only need to include the "meat" of your script. Since you are using the PowerShell type of a SQL Agent step it has already called the powershell.exe and imported the SQLPS module for you. So an example: enter image description here

With your code I believe you can simply configure the step as shown below: enter image description here

Best Answer

Related Solutions

Sql-server – SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints

Sql-server – SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Related Question