How to add users to MSSQL 2005 database using power shell

Question

Hi,

     I wanted to automate the DB restore from a back up and it works fine and I would like to add a specific group of users to the DB restored via Windows authentication. Could some help me with this using Power shell. I am using SMO objects in my powershell and I would like to know whether this can be achieved using SMO objects as well.

 

Any help would be much appreciated.

Thanks

Krish

Answer 1

Has the logins been created on the SQL instance? If its a matter of adding a database user for existing login you can add the following to your script:

$database = $server.databases["yourdatabasename"]
$name = 'yourwindowslogin'
$user = new-object ('Microsoft.SqlServer.Management.Smo.User') $database, $name
$user.Login = 'yourdomain\yourwindowslogin'
$user.DefaultSchema = 'dbo'
$user.Create()

$server variable is something you defined for the SQL instance.

Answer 2

Hi Normand,

                Thanks for your reply. The Scenario is like this. I have a back of of production which I will restore it on a devlopment Enivornment and I have got this working successfully. I will have to give db_readonly access to a list of users which I plan on keeping in a text file which I will read it and create this login after the prod_db restored. This process will happen on a regular basis and the user for the development enviroment will grow( this group of users are different from prod users). I will try your script and if there is any better way of achieving this please do let me know.

Look forward to hearing from you,

Thanks

KJR

Answer 3

It sounds like you already have a base script which uses Powershell and SMO?

If you you need to add a login using SMO it would look something like this:

$login = new-object ('Microsoft.SqlServer.Management.Smo.Login') $server, $name
$login.DefaultDatabase = 'master' 
#LoginType is 'WindowsUser' or WindowsGroup'
$login.LoginType = $logintype
$login.Create() 

The CodePlex I created, SQL Server Powershell Extensions has both and add-sqllogin and add-sqluser function which encapsulates this type of stuff. See http://sqlpsx.codeplex.com/

Answer 4

Hi Thanks for the info. The scenario has changed like the SQL server where I am restoring my DB has many other DB's. The server has 3 users like NU1, NU2, WGRUSR(windows group users)  and all these 3 groups already exists on the server logins. After my restore, I am trying to create these users in my Database and trying to add them to a db_datareader role. Strangely I am not able to add these 3 users to my DB and add them to the roles. Powershell thows error, which is not very helpful to know what exactly going on. I could not delete/recreate these 3 logins at the server level as these users will be using the other DB on the same server.

Would much appreciate any help / sugesstion to resolve this issue.

Thanks

KJR

Answer 5

Just so I undestand are you saying you have:

A Windows Group on SQL Server A with access to a database

The database is restored to Server B which already has the same Windows Group created as a SQL Server login

If this is the case there isn't anything to add/remove as the Windows group will have access on Server B just as they did on Server A.

Answer 6

Server A does not have the windows group

Server B has the windows group where the Database has been restored. I need to give these windowsgroup users access to the database that has been restored from Server A.

 I tried the following using SMO

$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $InstnsceName

$Database = $SQLInstance.Databases["$DatabaseName"]

$Database.Roles['db_datareader'].AddMember("DomainName\windowsGroup")

The above command adds a user to the Database that has been restored, but also creates a schema entry "Domain\windowsgroup" and default it to the scheama owned by this user.

I there anyway other way to establish the usermapping  to this usergroup to the database that has been restored from a different server.

Thanks

...KJR

Answer 7

The code I originally posted adds a user to the database. If you add a user in this way it shouldn't create a schema.

$database = $SQLInstance.databases["yourdatabasename"]
$name = 'yourwindowslogin'
$user = new-object ('Microsoft.SqlServer.Management.Smo.User') $database, $name
$user.Login = 'yourdomain\yourwindowslogin'
$user.DefaultSchema = 'dbo'
$user.Create()