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:


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

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


    foreach($Result in $Results)

    $Name = $Results.FirstName + $Results.LastName
    $password = ‘Password’

    # add extra info
    $user.Put(‘Description’,’Created by Script’)
    $flag=$user.UserFlags.Value -bor 0x800000

    # add user to mandatory local group

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


Leave a Reply

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