Using SETVAR SQLCMD variables to define logins and users

Question

I am using the new Visual Studio Team System 2008 Database Edition GDR (RTM version) and when I define SETVAR variables in my Database.sqlcmdvars file and try to use them in a create user statement such as:

CREATE USER [DbAdministrators] FOR LOGIN [$(DbAdministratorsLogin)];  (in file DbAdministrators.user.sql)

I get the following error:

Error 56 TSD03006: User: [NmasAdministrators] has an unresolved reference to Login [$(DbAdministratorsLogin)].

I have a similar problem when I try to define a login in a server project:

CREATE LOGIN [$(DbAdministratorsLogin)] FROM WINDOWS WITH DEFAULT_DATABASE = master, DEFAULT_LANGUAGE = us_english;

and I get the following error:

Error 57 TSD03073: '$(DbAdministrators)' is not a valid Windows NT name. You must specify the complete name: <domain\username>.

It appears that the SQLCMD mode is not being used to compile the login and user sql scripts that make up the database project files.  Is this behavior by design, is it a bug, or am I doing something wrong?

Answer 1

SQLCMD variables cannot be used in object identifiers (the names of an object type), only in object bodies for procs, triggers etc and inside pre and post deployment scripts.

SQLCMD variables only get substituted add deployment time, so you would be able to create an object, but it would never be able to update it since inside the model we do not have the value substituted, so it will always think that the object does not exist, which will cause a deployment failure.

-GertD

Answer 2

Thank you for the quick reply, Gert.  The problem with using SQLCMD variables to provide object identifiers is clear to me.  So, I understand now why I would not be able to use the variables to specify object identifiers for user or login objects.

However, shouldn't I be able to use the SQLCMD variables in defining role memberships?  In this case, I'd only be changing what database roles certain database users belong to based on the environment (a deployment-specific configuration).  This also doesn't work for me:

EXECUTE sp_addrolemember @rolename = N'gci_schemareader', @membername = N'$(DeveloperUser)';

results in:

Error 56 TSD03006: <unnamed> has an unresolved reference to object [$(DeveloperUser)].


If this doesn't work for the same reason as above (i.e., because you can't bind to an object-identifier specified in a SQLCMD variable except at deployment), then what is the appropriate or preferred way to specify environment-specific role membership in a database project?  We try, as a best practice within our organization, to make sure that all database privileges are assigned through database roles (built-in and custom) and then we add the appropriate users to the roles in a given environment/deployment.

We have different domain groups for our developer, test, and production administrator groups.  When I deploy my database project to the development environment, I want the "developer groups" to be assigned to the highly-privileged administrative roles in the database.  But when I deploy to the production environment I want the "production support" groups to be assigned to those highly-privileged roles and to have the developer groups assigned to roles that allow read-only access to data and schema/definition information.  What is the proper way to set that up in the GCR release of VSTE Database Edition?

Thank you very much for a great product and the timely support that your team provides here in the forums.

Answer 3

I have effectively the same question.  In my current situation the OS admins provide a Windows account that will access the database.  I never know what it is so I've attempted the following which fails.

I created a sqlcmdvars entry called $(Di_User).  My Di_User.user.sql script reads as follows.

CREATE USER

 

[$(Di_User)] FOR LOGIN [$(Di_User)];

But it gives me the following error.

Error 2 TSD03006: User: [$(Di_User)] has an unresolved reference to Login [$(Di_User)].

I can't point my project to production since it is locked down and can't be accessed from the Dev environment so pointing the project at Prod/QA/Staging and building a deployment script is not an option.

---

Joe Moyle

Answer 4

You would need to update the .dbschema file before deployment replacing the SQLCMD variables if you want that to work. Hold on a couple of days I am almost done with a tool that does this for you.

---

GertD @ www.DBProj.com

Answer 5

Thanks Gert but I couldn't wait.  My boss is breating down my neck.  I have a work around that does exactly what I want.  The project I'm working on involves updating another developers work and moving it into VS2008.  He did not have any roles in his scripts.  So I did the following.

I added a new database role called Data_Interceptor_Role to my database project.
I then modified the Database.sqlpermissions file so that my new role was the Grantee instead of the OS user.
I then created a Post-Deployment scrpt as follows.

CREATE USER

 

[$(DI_User)] FOR LOGIN [$(DI_User)] WITH DEFAULT_SCHEMA=[dbo];

GO

EXEC

 

sp_addrolemember N'Data_Intercept_Role', N'$(DI_User)'

GO

Now everything works as I desired and I get roles which I prefer over granting rights to individual users.

---

Joe Moyle

Answer 6

Did this tool ever get completed?  I'm trying to provide a variable for the domain of the logins being created, but whenever I deploy, it can't see that the user is already there and throws an error when it tries to create the user.

 

CREATE LOGIN [$(DomainName)\MyUser]
    FROM WINDOWS WITH DEFAULT_DATABASE = [myDB], DEFAULT_LANGUAGE = [us_english];

Answer 7

Joe, thank you, I would propose your post as answer if it wasn't already done. 

This will simplify my permission handling tremendously.