Specify Windows Username in SQL Script Without Knowing Machine Host Name

Question

Hello,

I'd like to reference a user account on a Windows machine, without knowing the host name of the machine.

Specifically, I am assigning permissions on a SQL server database, in a sql script, that will give permissions to a local user account on that machine.

For example: CREATE USER [UserA] FOR LOGIN [MACHINENAME\UserA] WITH DEFAULT_SCHEMA=[dbo]

This script is invoked as part of an installation process, so I don't know the 'MACHINENAME' ahead of time.

Is there a special token meaning 'the local machine' that I can use? Something like: '.\UserA', or 'localhost\UserA'?

Thanks!!

Answer 1

I haven't actually used this, but you may find what you need in the SQLCMD mode environment variables.

There is one in particular SQLCMDWORKSTATION that I think will give you what you want.

The MSDN documentation states that this variable defaults to the computer name.

You can also override these environment variables within a SQLCMD .ini file.

If that doesn't work, then you may still find a workable solution within SQLCMD mode - it lets you dynamically insert variables into SQL scripts.

Answer 2

Thanks -- I found a good way to do it, using the SERVERPROPERTY('MachineName'), as follows:

DECLARE @USERNAME NVARCHAR(MAX)
DECLARE @MACHINE_NAME NVARCHAR(MAX)
SELECT @MACHINE_NAME = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(MAX))
SET @USERNAME = @MACHINE_NAME + '\UserA'