July 4, 2016 at 2:43 am
Sounds like a headline from the onion.
All I want to do is add a new database role to a database and its not working. I understand the error, but I don't know why I'm getting it. It (the code) matches all the various examples I have found on the internet to compare with, so I'm just stuck. Whilst debugging, I can see that all the variables are showing the correct values, I just cant find an issue with it.
The error is specifically with this line...
$NewRole = New-Object Microsoft.SqlServer.Management.Smo.DatabaseRole $dbname, $RoleName
...but it seems ok according to the net and my PoSh books. Would love a bit of help.
Import-Module SQLPS -DisableNameChecking
$InstanceName="Inst_1"
$DBServerName="Serv_1"
$TargetServer= "$DBServerName\$InstanceName"
$RoleName= "Role1"
$dbname="TopTrumps"
Function fn-create-role-in-database ([String]$TargetServer, [String]$dbname, [String]$Rolename)
{
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $TargetServer
$NewRole = New-Object Microsoft.SqlServer.Management.Smo.DatabaseRole $dbname, $RoleName
$NewRole.Create()
}
fn-create-role-in-appname-database $TargetServer $dbname $RoleName
The error I am receiving is...
Exception : System.Management.Automation.MethodException: Cannot find an overload for "DatabaseRole" and the argument count: "2".
at System.Management.Automation.Adapter.GetBestMethodAndArguments(String methodName, MethodInformation[] methods,
PSMethodInvocationConstraints invocationConstraints, Object[] arguments, Object[]& newArguments)
at System.Management.Automation.DotNetAdapter.ConstructorInvokeDotNet(Type type, ConstructorInfo[] constructors, Object[] arguments)
at Microsoft.PowerShell.Commands.NewObjectCommand.CallConstructor(Type type, ConstructorInfo[] constructors, Object[] args)
TargetObject :
CategoryInfo : InvalidOperation: (:) [New-Object], MethodException
FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand
ErrorDetails :
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at fn-create-role-in-database, C:\Users\username\Documents\Create_DBRole.ps1: line 12
at <ScriptBlock>, C:\Users\username\Documents\Create_DBRole.ps1: line 16
PipelineIterationInfo : {}
PSMessageDetails :
Thank you.
D.
July 4, 2016 at 12:30 pm
Why anyone would use Posh to do such a trivial thing even across multiple databases is totally beyond me. My recommendation would be to go back to the simplicity of using SQL Server to do things in SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2016 at 5:22 pm
Hi Jeff,
If it was just that on its own Id agree, but this is a small cog in a much larger automation scenario.
Regards,
D
July 4, 2016 at 6:54 pm
Duran (7/4/2016)
Hi Jeff,If it was just that on its own Id agree, but this is a small cog in a much larger automation scenario.
Regards,
D
What is the "much larger automation scenario"? I ask not to be obnoxious. I ask because you just don't know what kind of rabbits the good folks on these fine forums can pull out of their hat.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2016 at 3:29 am
A couple of things:
1) It seems like in your function you create an object to reference your instance, but I could not tell that the server object was actually being used anywhere in your method call to create a db role.
2) Alternatively if you have to user PowerShell for this would it not be much easier to just use invoke-sqlcmd?
Joie Andrew
"Since 1982"
July 27, 2016 at 11:19 am
There are a lot of reasons for doing this through Powershell. We have automated quite a few things, including creating or duplicating a login. Being able to do add the login, create the user, add roles, etc. in one go has been time saving. Yes, we also have SQL scripts that do the same thing.
Anyway, here is the code we use to do the grant role:
function GrantRoleAccess
{
<#
.SYNOPSIS
The GrantRoleAccess function is used to facilitate the task of granting role access to a database user.
.DESCRIPTION
This function grants role access to a database user.
.PARAMETER LogFile
This is an output file, for execution messages.
.PARAMETER Verbose
This option writes output to Write-Host.
.PARAMETER GrantRoles
This is an object, containing information necessary to grant roles to a database user.
.INPUTS
GrantRoles Object is made up of the following:
InstanceName
Instance name of SQL Server, where role is to be created. This is required.
DatabaseName
Database name, where roles are to be added.
DBUserName
Database user name, to which the roles will be added.
DBRoles
DBRole objects, containing list of roles user is to be granted access to.
One role, per object.
$DBRoles = @()
$DBRole = New-Object PSObject @{
RoleName = "db_datareader"
}
$DBRoles += $DBRole
$GrantRoles = New-Object PSObject -Property @{
InstanceName = "Instance Name"
DatabaseName = "Database Name"
DBUserName = "DB User Name"
DBRoles = $DBRoles
}
.Outputs
Returns a single object, listing information concerning the granted access of roles.
SuccessfulRole indicates if granting of the role was successful.
# One role object, for each role added.
$NewRoleAccess = New-Object PSObject -Property @{
Role = $RoleName
SuccessfulRole = $SuccessfulRole
}
.Example
$InstanceName = "MyInstance"
$DatabaseName = "MyDatabase"
$DBUserName = "MyDBUserName"
# Set log file
$LogFile = "C:\TEMP\GrantRoles.Log"
if (Test-Path $LogFile)
{ Remove-Item $LogFile }
$DBRoles = @()
# Setup Role access
# Repeat this code as needed, one for each role
$DBRole = New-Object PSObject @{
RoleName = "db_datareader"
}
$DBRoles += $DBRole
$DBRole = New-Object PSObject @{
RoleName = "db_datawriter"
}
$DBRoles += $DBRole
$GrantRoles = New-Object PSObject -Property @{
InstanceName = $InstanceName
DatabaseName = $DatabaseName
DBUserName = $DBUserName
DBRoles = $DBRoles
}
$Roleccess = GrantRoleAccess -GrantRoles $GrantRoles -LogFile $LogFile -Verbose
$Roleccess
#>
param(
[parameter(Mandatory=$TRUE,Position=0)]
[PSObject] $GrantRoles,
[parameter(Mandatory=$TRUE,Position=1)]
[String] $LogFile
)
if($PSBoundParameters['Verbose'])
{ $Verbose = $TRUE }
else
{ $Verbose = $FALSE }
$Roleccess = @()
$InstanceName = $GrantRoles.InstanceName
$DatabaseName = $GrantRoles.DatabaseName
$DBUserName = $GrantRoles.DBUserName
$DBRoles = $GrantRoles.DBRoles
if($InstanceName)
{ $GrantRolesSrv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $InstanceName }
else
{
Write-LogFile $LogFile $Verbose "Instance Name was not provided. Could not connect."
return $Roleccess
}
if($DBRoles.Count -gt 0)
{
if($GrantRolesSrv.Databases[$DatabaseName].IsAccessible)
{
if($GrantRolesSrv.Databases[$DatabaseName].Users[$DBUserName])
{
$usr = $GrantRolesSrv.Databases[$DatabaseName].Users[$DBUserName]
foreach ($DBRole in $DBRoles)
{
$WrkRoleName = $DBRole.RoleName
$SuccessfulRole = $FALSE
$NewRoleAccess = New-Object PSObject -Property @{
Role = $WrkRoleName
SuccessfulRole = $SuccessfulRole
}
if($GrantRolesSrv.Databases[$DatabaseName].Roles[$WrkRoleName])
{
if($usr.IsMember($WrkRoleName) -ne $true)
{
$WrkRole = $GrantRolesSrv.Databases[$DatabaseName].Roles[$WrkRoleName]
$WrkRole.AddMember($DBUserName)
Write-LogFile $LogFile $Verbose "$InstanceName : User $DBUserName has been added to role $WrkRoleName on database $DatabaseName"
$SuccessfulRole = $true
}
else
{
Write-LogFile $LogFile $Verbose "$InstanceName : User $DBUserName is already a member of role $WrkRoleName on database $DatabaseName"
$SuccessfulRole = $true
}
}
else
{
Write-LogFile $LogFile $Verbose "$InstanceName : Role $WrkRoleName does not exist on $DatabaseName. No action taken"
$SuccessfulRole = $false
}
$NewRoleAccess.SuccessfulRole = $SuccessfulRole
$Roleccess += $NewRoleAccess
}
}
else
{
Write-LogFile $LogFile $Verbose "$InstanceName : $DBUserName user does not exist on database $DatabaseName. User has not been given role access"
}
}
else
{
Write-LogFile $LogFile $Verbose "$InstanceName : Database does not exist. Could not update user role access"
return $Roleccess
}
}
else
{
Write-LogFile $LogFile $Verbose "$InstanceName : There are no roles to process for $DBUserName on database $DatabaseName. No action taken"
}
return $Roleccess
} # End GrantRoleAccess
July 27, 2016 at 11:29 am
ok, I'm an idiot. Just realized the ask was for adding a role, not a member to a role. That one we didn't automate.
August 3, 2016 at 8:03 am
You're passing a DBname, but the class expects a database object:
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $TargetServer
$objDB = $Server.Databases[$dbname]
$NewRole = New-Object Microsoft.SqlServer.Management.Smo.DatabaseRole ($objDB, $RoleName)
$NewRole.Create()
August 26, 2016 at 1:30 am
Thanks for the replies here, sorry I have not got back, I did actually work this out in the end. When I have finished the whole project I am going to go over what it is I have automated. I'm waiting till the end because I keep getting extra bits to do just as I think I'm finishing up! In the meantime I have a new issue which I'll start a new thread for.
Thanks again.
Regards,
D.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply