SQL PowerShell - Add Windows User as SysADM

SQL PowerShell - Add Windows User as SysADM

OK, So I guess its finally time to start Teaching myself Powershell commands to enable me to administer SQL Server on Windows Core.

I will share my findings along the way.

The first thing I need to know how to do its add  a Windows Domain User as a SQL Server SysAdm.

So here goes.

I borrowed the following code off the Web somewhere, but could not get the AddToRole statement to work, originally it was placed after the Create() Statement, I found that by moving the Create() to the End of the Statement, everything worked as it should.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$SqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'SERVERNAME\INSTANCENAME'

$SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $SqlServer, 'DOMAIN\USERNAME'

$SqlUser.LoginType = 'WindowsUser'

$SqlUser.AddToRole('sysadmin')

$SqlUser.Create()


OK, so two days in and I'm starting to get the hang of this now, I have realised that what I have done above (although it does work) is basically wrong.

We have to do this in stages : -

#LOAD SQL Server Objects

Load the SQL Server SMO Libraries to Powershell

#CONSTRUCTORS

Define the type of Object we want to work with, Database, Login etc

#PROPERTIES

Set the Values for the Properties, Login type for example

#CREATE

Run the Create() Method to Create the Object

#METHODS

Now the object has been created, we can run Methods to amend the Objects, such as AddRole.

#EVENTS

Check Event such as HasDataChanged

So the Add Windows User as SQL Sysadm, should look like this :-

#LOAD SQL Server Objects

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#CONSTRUCTORS

$Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'ServerName\InstanceName,Port'

$Login = New-Object Microsoft.SqlServer.Management.Smo.Login($Server, "Domain\User")

# PROPERTIES

$Login.LoginType = 'WindowsUser'

#CREATE

$Login.Create()

#METHODS

$Login.AddToRole('sysadmin')

#EVENTS

If you refer to the Microsoft Documentation on Microsoft.SqlServer.Management.Smo :-

http://technet.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.aspx

We can use the above as the basis to do pretty much anything we desire in SQL Server with PowerShell.

So I don't think I am there yet, there will be plenty more to learn on the way, however, I think that I have passed a major Milestone in understanding just how these SQL PowerShell commands are constructed.