xSQLServer is a great DSC module for deploying SQL Server up to version 2014. Currently it does not work with SQL Server 2016. One missing part of this module is, that you cannot assign SQL Server roles to a login account on a server level, it only works on a database level. So one way to assign SQL Server roles to a SQL login is to use the Microsoft.SqlServer.Smo assembly. It works like that…

 [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
 $SQLServer = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “localhost\instance"
 $SQLServer.Roles["serveradmin"].AddMember(“Testuser”)
$SQLServer.Roles["dbcreator"].AddMember(“Testuser”)

This would assign “serveradmin” and “dbcreator” role to “Testuser”.

Leave a Comment