2

I have a table of system users each of whom need a database user of their own that the system uses to access the DB(Not my idea..). The system has it's own login. This is creating a problem with the SQL AlwaysOn distributed server setup we're testing because users won't sync between databases. So although the system creates the database users, they won't propogate to the secondary node and when a fallover happens these users can't log in.

So that's my justification for wanting to write a script that creates a DB user from a table in the DB. So, why doesn't the following work and how could I make it work? 

DECLARE @foo VARCHAR(250)
SET @foo = (SELECT USER_NAME FROM USER_TABLE WHERE USER_ID = 1337)    
CREATE USER @foo

Currently I get incorrect syntax near '@foo'

Jansky
  • 1,455
  • 1
  • 17
  • 33
  • Why *doesn't* it work? What error are you getting? – RBarryYoung Jun 11 '13 at 15:00
  • 1
    You can't combine variables and commands like that. SQL will not replace `@foo` with it's value, but will try to create a user named `@foo` (which is illegal name). You should use dynamic sql to achieve this. Dave K has just posted the answer showing it. – Nenad Zivkovic Jun 11 '13 at 15:13
  • Alternatively, can you put all of these users in an AD group, add that group *once* on the secondary and call it done? – Ben Thul Jun 11 '13 at 16:28
  • Really good idea Ben but wouldn't work in our particular case, because the user accounts are SQL Authentication accounts generated by a piece of software. We're thinking about using a contained database. – Jansky Jul 10 '13 at 13:35

1 Answers1

2
DECLARE @foo VARCHAR(250), @execString NVARCHAR(MAX)
SET @foo = (SELECT USER_NAME FROM USER_TABLE WHERE USER_ID = 1337)    

SET @execString = 'CREATE USER ' + @foo
EXECUTE sp_executesql @execString
Dave K
  • 1,845
  • 1
  • 11
  • 9