PoshCode Logo PowerShell Code Repository

Add User to database rol by graebener 3 weeks ago (modification of post by SQLDBAwithabeard view diff)
diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/6765"></script>download | new post

############################################################################################# #

  1. NAME: Add-UserToRole.ps1
  2. AUTHOR: Rob Sewell http://sqldbawithabeard.com
  1. DATE:11/09/2013
  2. #
  3. COMMENTS: Load function to add user or group to a role on a database
  4. #
  5. USAGE: Add-UserToRole fade2black Aerosmith Test db_owner

  1. #############################################################################################
  2. #
  3. # NAME: Add-UserToRole.ps1
  4. # AUTHOR: Rob Sewell http://sqldbawithabeard.com
  5. # DATE:11/09/2013
  6. #
  7. # COMMENTS: Load function to add user or group to a role on a database
  8. #
  9. # USAGE: Add-UserToRole fade2black Aerosmith Test db_owner
  10. #        
  11.  
  12. Function Add-UserToRole ([string] $server, [String] $Database , [string]$User, [string]$Role)
  13. {
  14. $Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
  15. #Check Database Name entered correctly
  16.     if($svr.Databases.name -notcontains $Database)
  17.         {
  18.         Write-Host " $Database is not a valid database on $Server"
  19.         Write-Host " Databases on $Server are :"
  20.         $svr.Databases|select name
  21.         break
  22.         }
  23. #Check Role exists on Database
  24.         $db = $svr.Databases[$Database]
  25.     if($db.Roles.name -notcontains $Role)
  26.         {
  27.         Write-Host " $Role is not a valid Role on $Database on $Server  "
  28.         Write-Host " Roles on $Database are:"
  29.         $db.roles|select name
  30.         break
  31.         }
  32.     if(!($svr.Logins.Contains($User)))
  33.         {
  34.         Write-Host "$User not a login on $server create it first"
  35.         break
  36.         }
  37.     if (!($db.Users.Contains($User)))
  38.         {
  39.         # Add user to database
  40.  
  41.         $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
  42.         $usr.Login = $User
  43.         $usr.Create()
  44.  
  45.         #Add User to the Role
  46.         $Rol = $db.Roles[$Role]
  47.         $Rol.AddMember($User)
  48.         Write-Host "$User was not a login on $Database on $server"
  49.         Write-Host "$User added to $Database on $Server and $Role Role"
  50.         }
  51.         else
  52.         {
  53.          #Add User to the Role
  54.         $Rol = $db.Roles[$Role]
  55.         $Rol.AddMember($User)
  56.         Write-Host "$User added to $Role Role in $Database on $Server "
  57.         }
  58. }

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me