Creating SQL Server login via script using variable

Question

I am trying to create a SQL Server login following the creation of a database through script. The login is local to the host PC and this script will be run on multiple hosts.

What I want to do is the following:

USING MyDatabase
CREATE MyUser FOR LOGIN USER <computer name>/MyUser

What I don't know how to do is incorporate the computer name (ex. Location0001) into this statement.

The login does exist on each machine AND is already listed on the SQL Server as it has access to other databases there.

If it's relevant, the script should be able to run on SQL Server 2008.

The login would look like this for each machine:

Location0001\MyUser
Location0002\MyUser
Location0999\MyUser

Any help would be appreciate, thanks.

Edit: My final solution based on the accepted answer is as follows:

DECLARE @cmd VARCHAR(200)
SET @cmd = N'CREATE USER [MyUser] FOR LOGIN [' + HOST_NAME() + '\MyUser]'
EXEC (@cmd)

Michal Barcik · Accepted Answer · 2011-09-27 18:23:42Z

11

You can use the @@servername property to get the name of the machine: select @@servername

declare @cmd varchar(200), @username varchar(50)

set @username = 'testuser'
set @cmd = ' 
   USE MyDatabase
   CREATE USER MyUser FOR LOGIN '+@@servername+'\'+@username 
PRINT @cmd
EXEC (@cmd)

answered Sep 27, 2011 at 18:23

Michal Barcik

6624 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Terry Over a year ago

I didn't think of using inline SQL. Thank you, I had to made some syntax changes but the concept did work.

user596075 Over a year ago

Consider using sp_executesql as opposed to EXEC(UTE). It allows for parameterized queries which eliminates the possibility of SQL Injection as well as utilizes plan reuse from the plan cache.

Cozzamara Over a year ago

This suggestion will only work for default instance. For named instances, @@servername will return 'MACHINE\INSTANCE', so CREATE USER will fail. You need either HOST_NAME() like OP suggested, or ServerProperty('MachineName'), depending on what you're trying to achieve. I know this has been answered - I am writing for those who get here from web searches.

Tar Over a year ago

user596075: the whole reason for constructing this is that the CREATE USER statement can't be parametrized.

Benrobot Over a year ago

sp_executesql does not work for this example. But yes, use sp_executesql for cases where actual parameters are passed.

Collectives™ on Stack Overflow

Creating SQL Server login via script using variable

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related