Powershell Creating SQL login

Hi all

I've made a script in Powershell 2.0 for creating users in Microsoft SQL Server 2008.
Now, everything is fine. But I want to script 'user mapping' for new created logins.
So you know, if you choose properties of sql user and choose 'User Mapping' and select a database to map for the selected user.

I searched on Google, but doesn't find anything.. Can someone help me for powershell 2.0?

Kind Regards
Triforce2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dorababu MSenior Software EngineerCommented:
So you need to manually select the database and assign created user with a role?
0
Triforce2000Author Commented:
Made a powershell script for making a user logon in sql but when i go to properties of this user, i have manually map database for this user for now, but i want this option in powershell..
0
Dorababu MSenior Software EngineerCommented:
You need to apply for any specific database or all databases?
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Triforce2000Author Commented:
A specific database
0
Dorababu MSenior Software EngineerCommented:
Try this and let me know
## Creating database user and assigning database role    

#get variables
$instanceName = "localhost"
$SqlDBName = "TestDB"
$roleName = "db_backupoperator"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($instanceName)
 
# get all of the current logins and their types
$SqlServer.Logins |
    Select-Object Name, LoginType, Parent
 
# create a new login by prompting for new credentials
$NewLoginCredentials = Get-Credential -Message "Enter credentials for the new login"
$NewLogin = New-Object Microsoft.SqlServer.Management.Smo.Login($SqlServer, $NewLoginCredentials.UserName)
$NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
$NewLogin.Create($NewLoginCredentials.Password)
 
# create a new database user for the newly created login
$NewUser = New-Object Microsoft.SqlServer.Management.Smo.User($SqlServer.Databases[$SqlDBName], $NewLoginCredentials.UserName)
$NewUser.Login = $NewLoginCredentials.UserName
$NewUser.Create()
$NewUser.AddToRole($roleName) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Triforce2000Author Commented:
Exception calling "Create" with "0" argument(s): "Create failed for User 'testuser'. "
At line:34 char:16
+ $NewUser.Create <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
 
Exception calling "AddToRole" with "1" argument(s): "An exception occurred while executing a Transact-SQL statement or batch."
At line:35 char:19
+ $NewUser.AddToRole <<<< ($roleName)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
0
Dorababu MSenior Software EngineerCommented:
can you import the module and give a try
Import-module SQLPS

Open in new window

0
Triforce2000Author Commented:
Doesnt help and SQLPS isn't for powershell 2.0 i think?

Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.
At line:1 char:14
+ Import-module <<<<  SQLPS
    + CategoryInfo          : ResourceUnavailable: (SQLPS:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand
0
Triforce2000Author Commented:
i've added different types of assemblys
 #Load Assemblys
  Add-Type -AssemblyName  "Microsoft.SqlServer.ConnectionInfo,  Version=10.0.0.0,  Culture=neutral,  PublicKeyToken=89845dcd8080cc91"   -ErrorAction Stop

  Add-Type -AssemblyName  "Microsoft.SqlServer.Smo,  Version=10.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"  -ErrorAction Stop

  Add-Type -AssemblyName  "Microsoft.SqlServer.SMOExtended,  Version=10.0.0.0, Culture=neutral,  PublicKeyToken=89845dcd8080cc91"   -ErrorAction Stop

  Add-Type -AssemblyName  "Microsoft.SqlServer.SqlEnum,  Version=10.0.0.0, Culture=neutral,  PublicKeyToken=89845dcd8080cc91"   -ErrorAction Stop

  Add-Type -AssemblyName  "Microsoft.SqlServer.Management.Sdk.Sfc,  Version=10.0.0.0,Culture=neutral,   PublicKeyToken=89845dcd8080cc91" -ErrorAction  Stop
0
Dorababu MSenior Software EngineerCommented:
Did that worked?
0
Triforce2000Author Commented:
yes, the problem was that the user already existed in db with al my tests.. So the mapping doesn't worked and always gave that error..

I ran $error[0] | format-list -force and this gave me more explanation about the error

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.