יום שני 06 פברואר 2012 00:08
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.
יום שני 06 פברואר 2012 03:17
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:$server variable is something you defined for the SQL instance.
$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()
יום שלישי 07 פברואר 2012 03:06
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,
שבת 11 פברואר 2012 18:57
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:
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/
$login = new-object ('Microsoft.SqlServer.Management.Smo.Login') $server, $name
$login.DefaultDatabase = 'master'
#LoginType is 'WindowsUser' or WindowsGroup'
$login.LoginType = $logintype
יום חמישי 16 פברואר 2012 21:24
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.
- נערך על-ידי Krish JR יום חמישי 16 פברואר 2012 21:25
יום שישי 17 פברואר 2012 21:39
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.
יום שני 20 פברואר 2012 16:45
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"]
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.
יום רביעי 22 פברואר 2012 02:25
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.
SQLInstance.databases new object ( ) , .Login .DefaultSchema .Create()