PowerShell script to add first domain user [permissions issue]

Question

I have a SQL Server 2014 VM in Azure, created from the Azure store. I want to add a PowerShell CustomExtension to set up my database environment as soon as the new VM is available. I have an issue with permissions. If I run the PowerShell code below as local admin, I get a message that the Windows NT User or group cannot be found, as that local admin does not have domain admin permissions. If I run the below PowerShell as a domain admin, then that domain admin does not have permission to log on to SQL Server, which is actually the task I am trying to achieve.

cls
import-module sqlps –DisableNameChecking -Verbose:$false | Out-Null
$sql = "USE [MASTER]
GO
CREATE LOGIN [ABC.PROD\My Group Name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO"
Invoke-Sqlcmd -Query $sql

If above run as domain admin I get this error

Invoke-Sqlcmd : Login failed for user 'ABC\myadminusername'.

If above run as local admin I get this error

Invoke-Sqlcmd : Windows NT user or group 'ABC.PROD\My Group Name' not found. Check the name again.

Is there an easy way out of this catch 22 using some handy PowerShell? If I do this manually, I would just log on to SQL Server as the local admin, add user, list locations, and supply domain admin credentials when asked. But I cannot see any way to supply those domain admin credentials to this SQL command.

Thank you, David

MadBison MadBison · Accepted Answer · 2017-11-14T09:50:57

OK, I actually found the issue while I was writing this question. Given the question was already written, I figured I would go ahead and post it and give the answer in case this helps someone else.

The problem appears to have been that I should not provide a fully qualified domain name. The below code worked when run as the local admin. I just changed "ABC.PROD\My Group Name" to "ABC\My Group Name", and it worked.

cls
import-module sqlps –DisableNameChecking -Verbose:$false | Out-Null
$sql = "USE [MASTER]
GO
CREATE LOGIN [ABC\My Group Name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO"
Invoke-Sqlcmd -Query $sql

PowerShell script to add first domain user [permissions issue]

1 Answers