asked on

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

Dorababu M

So you need to manually select the database and assign created user with a role?

Triforce2000

ASKER

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..

Dorababu M

You need to apply for any specific database or all databases?

Triforce2000

ASKER

A specific database

ASKER CERTIFIED SOLUTION

Dorababu M

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Triforce2000

ASKER

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

Dorababu M

can you import the module and give a try

Import-module SQLPS

Open in new window

Triforce2000

ASKER

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

Triforce2000

ASKER

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

Dorababu M

Did that worked?

Triforce2000

ASKER

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!