1

I am creating user creation script in an SQL Server 2008 Database Server project in Visual Studio 2010.

The script looks like this...

CREATE LOGIN Domain\User FROM WINDOWS

I will be developing on both my desktop PC and laptop. Neither run on a domain and use local users e.g. DESKTOP\TestUser and LAPTOP\TestUser.

What I need to do is to somehow inject a variable into the script that automatically sets the domain name. I believe in SQL Server this is @@SERVERNAME. Then I can work on both my PCs and the script will work. At present if I run it on say my laptop I get an error as DESKTOP\TestUser is not a Windows Principal on my laptop.

I have looked at the .sqlcmdvars files and I can set a variable in there that gets injected into the script. However I cannot seem to get this to something dynamic such as @@SERVERNAME.

I have also looked at running EXEC from my post deployment script but I get an error saying that this command is not valid in this context - I believe that means that EXEC is not accepted in the VS2010 DB project.

Remotec
  • 10,304
  • 25
  • 105
  • 147

1 Answers1

1

You can use the following script to achieve that. It works for me well and creates a login for the local user:

declare @sql nvarchar(max) = N'create login [' + @@SERVERNAME + N'\TestUser] from windows'
exec(@sql)
Szymon
  • 42,577
  • 16
  • 96
  • 114
  • Good idea but sadly won't run as part of a DB deployment from visual studio. Gives an error "Only one statement is allowed per batch." – Remotec Nov 03 '13 at 08:31
  • 1
    Maybe you should keep logins outside of VS (not in the db project). This is what we're doing. – Szymon Nov 03 '13 at 08:43
  • 1
    Yes agreed. All of the objects can be in the DB Project and then you can run a permissions script afterwards. Thanks. – Remotec Nov 04 '13 at 08:12