Script Logging on to SQL Server with Windows Authentication without a Machine Name

Question

I want to write a SQL 2005 script to create a new login using Windows authentication. A Windows user is a local account (not a domain account). A local account with the same name exists on many SQL Server machines, and I want to run the same script for all of them.

It seemed simple enough:

CREATE LOGIN [MyUser]
FROM WINDOWS

However, this does not work! SQL returns an error sayingGive the complete name: <domain\username>.

Of course, I can do this for one machine, and it works, but the same script will not work on other machines.

Answer 1

, sp_executesql - , . , @@SERVERNAME , SQL, .

DECLARE @loginName SYSNAME
SET @loginName = CAST(SERVERPROPERTY('MachineName') AS SYSNAME) + '\MyUser'

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @loginName)
BEGIN
    DECLARE @sql NVARCHAR(1000)
    SET @sql = 'CREATE LOGIN [' + @loginName + '] FROM WINDOWS'
    EXEC sp_executesql @sql
END

Answer 2

:

DECLARE @sql nvarchar(1000)
SET @SQL = 'CREATE LOGIN [' + @@SERVERNAME + '\MyUser] FROM WINDOWS'
EXEC sp_executeSQL @SQL