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.


2 Answers 2


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 | % {
  • Please don’t add just a link as answer to a question. If the link breaks, the answer you provided will not be useful for future visitors. Provide an answer of your own and use the link as reference. If needed, you can block quote the content from your link. More information is found in our help center, especially How do I write a good answer
    – Benny Skogberg
    Feb 4, 2016 at 7:02
  • 1
    @BennySkogberg Updated the answer
    – Citizen SP
    Feb 4, 2016 at 7:56
  • 1
    @CitizenDB Removed the notice, and upvoted. Thanks!
    – Benny Skogberg
    Feb 4, 2016 at 8:58
  • This does not answer the specific question related to SharePoint databases.
    – Barry MSIH
    Feb 5, 2016 at 20:35

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" 

$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


Your Answer

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

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