Add-SQLDatabaseRoleMember not working

Developer
May 13, 2011 at 7:41 PM

I love being able to manage SQL Server with PowerShell and without having to learn the intricacies of SMO (for the moment).  I've got a question about Add-SQLDatabaseRoleMember behavior.  I'm trying to add a user to the db_owner role on a SQL 2005 database, but nothing happens.  I get no error messages, but the role membership never changes.  Here's what I'm doing:

$db = Get-SQLDatabase "Z002\sql2K5" "UserDB"
Add-SQLUser -dbName $db -name "NewUser"
Add-SQLDatabaseRoleMember -dbname $db -name "NewUser" -rolename "db_owner"

When I check the role membership with the following, I don't see the user added to the role:

Get-SQLDatabaseRole -dbname $db | ? { $_.name -eq "db_owner" }

I tried re-instantiating the database object with Get-SQLDatabase, but I still don't see the new role member.  I also tried with different roles to see what would happen, but still no joy.  Finally, I tried directly with the AddMember() method on the Role object, and it also failed with no error.

I am running with sysadmin permissions and using SQLPSX v2.3.2.1 and the version of SMO installed with the SQL 2008 R2 client tools.  Any thoughts are appreciated?

Jamie

Coordinator
May 13, 2011 at 8:27 PM
Should you be using "UserDB" instead of $db as the -dbname parameter?

On May 13, 2011 1:41pm, jfbradfo <notifications@codeplex.com> wrote:
>
> From: jfbradfo
>
> I love being able to manage SQL Server with PowerShell and without having to learn the intricacies of SMO (for the moment). I've got a question about Add-SQLDatabaseRoleMember behavior. I'm trying to add a user to the db_owner role on a SQL 2005
> database, but nothing happens. I get no error messages, but the role membership never changes. Here's what I'm doing:
>
> $db = Get-SQLDatabase "Z002\sql2K5" "UserDB"
>
> Add-SQLUser -dbName $db -name "NewUser"
>
> Add-SQLDatabaseRoleMember -dbname $db -name "NewUser" -rolename "db_owner"
>
> When I check the role membership with the following, I don't see the user added to the role:
>
> Get-SQLDatabaseRole -dbname $db | ? { $_.name -eq "db_owner" }
>
> I tried re-instantiating the database object with Get-SQLDatabase, but I still don't see the new role member. I also tried with different roles to see what would happen, but still no joy. Finally, I tried directly with the AddMember() method
> on the Role object, and it also failed with no error.
>
> I am running with sysadmin permissions and using SQLPSX v2.3.2.1 and the version of SMO installed with the SQL 2008 R2 client tools. Any thoughts are appreciated?
>
> Jamie
>
>
> Read the full discussion online.
> To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
> To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
> You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
> Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com
>
>
Developer
May 13, 2011 at 8:35 PM

Either should work, per the docs. 

The problem seems to be resolved, as I had to reboot my laptop.  I did queried the db_owner role for the database, and, voila, the user I had added was there now.  I'm now wondering if there was something funky going on with my PowerShell host or with my laptop that was preventing getting updated object information from the server. 

In any case, this is no longer an issue.

 

Jamie

Coordinator
May 13, 2011 at 8:48 PM
Glad it's working. I haven't delved much into the SQLServer module so I was confused by the parameter naming.

On May 13, 2011 2:35pm, jfbradfo <notifications@codeplex.com> wrote:
>
> From: jfbradfo
> Either should work, per the docs.
>
> The problem seems to be resolved, as I had to reboot my laptop. I did queried the db_owner role for the database, and, voila, the user I had added was there now. I'm now wondering if there was something funky going on with my PowerShell host or with my laptop that was preventing getting updated object information from the server.
>
> In any case, this is no longer an issue.
>
>
>
> Jamie
>
> Read the full discussion online.
> To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
> To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
> You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
> Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com
>
>
Developer
May 13, 2011 at 10:01 PM

OK, maybe I spoke to too soon.  In the new session, I tried to remove another user I'd added to test whether the problem was with a domain login vs a SQL login.  The Remove-SQLDatabaseRoleMember function seems to do the same thing that the Add-SQLDatabaseRoleMember; that is, it seems to function correctly, but a check of the role membership via Get-SQLDatabaseRole still shows the account in the role.  I tried reloading the database object via Get-SQLDatabase, but there was no change.  When I started a new PowerShell session, however, and checked role membership in that session, it was correct; the account had been removed.

This must be an SMO thing, and I think it's related to the SMO connection object and probably connection pooling.  I'm going to play with this a bit and followup next week.

Jamie

Coordinator
May 13, 2011 at 10:22 PM
As I said, I'm not terribly familiar with the SQLServer module. You're probably right that somehow the connection and/or other objects are being cached. Chad will probably see this thread and let us both know how it's supposed to work.

On May 13, 2011 4:01pm, jfbradfo <notifications@codeplex.com> wrote:
>
> From: jfbradfo
> OK, maybe I spoke to too soon. In the new session, I tried to remove another user I'd added to test whether the problem was with a domain login vs a SQL login. The Remove-SQLDatabaseRoleMember function seems to do the same thing that the Add-SQLDatabaseRoleMember; that is, it seems to function correctly, but a check of the role membership via Get-SQLDatabaseRole still shows the account in the role. I tried reloading the database object via Get-SQLDatabase, but there was no change. When I started a new PowerShell session, however, and checked role membership in that session, it was correct; the account had been removed.
>
> This must be an SMO thing, and I think it's related to the SMO connection object and probably connection pooling. I'm going to play with this a bit and followup next week.
>
> Jamie
>
> Read the full discussion online.
> To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
> To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
> You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
> Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com
>
>
Coordinator
May 14, 2011 at 8:03 PM

I see the problem.

I've got a test hardness which is included in the source code section under trunk\TestScripts\Test.SQLServer.ps1 -- There's one test script for each module. I use PSUnit (a PowerShell unit testing framework). Here's a an excerpt of the code to test adding a role member which has been modified to remove dependencies on PSUnit:

 import-module sqlserver
    #Arrange
    $server = get-sqlserver "$env:computername\r2"
    if (!($server.Logins["TestPSUnit"]))
    { Add-SqlLogin "$env:computername\r2" "TestPSUnit" "SQLPSXTesting" 'SqlLogin' }
    $database = get-sqldatabase $server "pubs"
    if (!($database.Users["TestPSUnit"]))
    { Add-SqlUser -dbname $database -name "TestPSUnit" }
    if (!($database.Roles["TestPSUnitDBRole"]))
    { Add-SqlDatabaseRole -dbname $database -name "TestPSUnitDBRole" }
    if (($database.Roles["TestPSUnitDBRole"]).EnumMembers() | where {$_.Name -eq "TestPSUnit"})
    {Remove-SqlDatabaseRoleMember -dbname $database -name "TestPSUnit" -rolename "TestPSUnitDBRole" }
    
    #Act
    Add-SqlDatabaseRoleMember -dbname $database -name "TestPSUnit" -rolename "TestPSUnitDBRole" 
    $Actual = $?
    Write-Debug $Actual
    #Assert Add-DatabaseRoleMember succeeded
    $Actual
    
    #To see results we need to refresh database object
    $database.Refresh()
    #We can then see the role member was added using SMO EnumMembers directly
    ($database.Roles["TestPSUnitDBRole"]).EnumMembers() 
    
    #SQLPSX uses several global variables to cache database security settings. 
    #This is good and bad. Its good in that if you are generating a security report you don't have to re-enum
    #security settings for each object. Its bad in that once you can a security setting you'll need to remove the cache variable
    #the next time it runs this will be repopulated. A better solution is for us to fix the code to do this in the function
    #Here's a workaround for now:
    #See all SQLPSX global variables
    Get-Variable -name __SQLPSX*
    #Remove the rolemember variable
    Remove-Variable -Name __SQLPSXDatabaseRoleMember
    #re-run get-sqldatabaserole
    Get-SqlDatabaseRole -database $database | where {$_.name -eq "TestPSUnitDBRole"} | select name, members

So the problem is two-fold. First when you use SMO you either need to refresh an object by calling its refresh method i.e. $database.refresh() and second some caching SQLPSX is doing as described. I think the "best" solution is to handle both of these issues within the SQLPSX function add-sqldatabaserolemember by adding a couple of lines:

sqlserver.psm1 file Add-SqlDatabaseRoleMember function:

  

        if ($role)
        { 
            $role.AddMember($name)
            $database.Refresh()
            Remove-Variable -Name __SQLPSXDatabaseRoleMember -ErrorAction "SilentlyContinue"
        }


I'll get this into the next release along with making similar type changes for the other add-* functions. In the meantime if you want to implement the fix, you can.

Coordinator
May 14, 2011 at 8:04 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
May 14, 2011 at 8:41 PM
Thanks, Chad...I figured you knew the answer.

On , cmille19 <notifications@codeplex.com> wrote:
>
> From: cmille19 This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
>
> Read the full discussion online.
> To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
> To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
> You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
> Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com
>
>