How to grant user DB_Owner on all SharePoint 2013 databases

Question

For many admin tasks for SharePoint 2013 using PowerShell you need to have DB_Owner for all SharePoint databases. The Add-SPShellAdmin command no longer granted DB_Owner to the SharePoint databases, instead grants the user the "SharePoint_Shell_Access" role.

I want to run a scheduled task that will automatically add a list of users as DB_Owner to all of the SharePoint database.

How can this be done.

Answer 1

You can use the script to give specific users the db_owner role. Don't forget to dd all users to users.txt.

  [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | out-null

    $Users = get-content c:\users.txt
Foreach ($User in $Users){

    $InstanceName = ""
    $NewOwnerLoginName = $user;

    $smosvr = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $InstanceName
    $agent = $smosvr.JobServer;

    $agent.Jobs | % {
        $_.set_OwnerLoginName($NewOwnerLoginName);
        $_.Alter();
        }

Answer 2

This might work

###Public Variables
$SQLServer = "Server\Instance" 
$MyUser = "domain/account"
$spDatabases = Get-SPDatabase

####Private Variables
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Integrated Security = True" 

$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection

ForEach($spDatabase in $spDatabases)
{
    $SQLDBName = $spDatabase.Name   
    $SqlQuery = "use $SQLDBName; ALTER ROLE db_owner ADD MEMBER [$MYUSER];"
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.ExecuteNonQuery() | Out-Null
}

$SqlConnection.Close()