Using Powershell to create Local Users from AW Customers

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:

PowerShell_Script1

Just to take a peek at what this script generates, here is a screendump:

Users

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!?

Loading

2 thoughts on “Using Powershell to create Local Users from AW Customers

  1. SQLDBAwithTheBeard Reply

    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)
    }

    • Jens Vestergaard Post authorReply

      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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.