As a part of my upcomming presentation at SqlSaturday Cambridge, I will probably be needing a lot of users to demo some aspects of security in Analysis Services. Therefore I have created a Powershell script, my first actually, to generate a bunch of users. The first source for a lot of users that came to mind was the company AD, but that probably wouldn’t be given the green light, if I asked. So what to do? Adventure Works to the rescue!
The Customer dimension in the Adventure Works database has roughly 18.000 entries. This would make a nice pile of users for a demo. So, connecting to the database, selecting the distinct customers and just adding them as local users can actually be done rather quickly through Powershell. The run-time is just around 10 minutes, to create or delete.
The script looks like this:
Just to take a peek at what this script generates, here is a screendump:
I have attached both a create and a delete script: PS_Create Local Users From AW
As this is my first script, I’d be happy to hear if there is a better way of doing this!?
Nice script
Personally I would have used Invoke-SQLcmd as follows (I haven’t tested this just written it out)
$server = ‘Demo’
$query = ” SELECT DISTINCT FirstName, LastName FROM dbo.DimCustomer”
$Db = ‘AdventureWorksDW2012’
$Results = Invoke-Sqlcmd -server $server -Database $db -query $query
and then either
foreach($Result in $Results)
{
$Name = $Results.FirstName + $Results.LastName
Net USER $Name /ADD
net GROUP “Users” $Name /add
}
or
foreach($Result in $Results)
{
$Name = $Results.FirstName + $Results.LastName
$server=[adsi]”WinNT://$env:$Demo”
$user=$server.Create(“User”,”$name”)
$password = ‘Password’
$user.SetPassword($password)
$user.SetInfo()
# add extra info
$user.Put(‘Description’,’Created by Script’)
$flag=$user.UserFlags.Value -bor 0x800000
$user.put(‘userflags’,$flag)
$user.SetInfo()
# add user to mandatory local group
$group=[adsi]”WinNT://$env:computername/Users,Group”
$group.Add($user.path)
}
Hi Rob
Since this was my first ever Powershell script, I can handle a little critisism 😉
Didn’t think of the Invoke-SqlCmd method, perhaps because I come from a .Net background, so I know that toolbox.
I will try out the different dialects you have enlisted, to see if there is any difference in behaviour.
Thanks for the input, really appreciate it!
Br
Jens