jeudi 1 janvier 2009 01:12I 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:
CREATELOGIN [$(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?
Toutes les réponses
jeudi 1 janvier 2009 22:58SQLCMD 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.
- Marqué comme réponse Barclay HillMicrosoft Employee, Moderator vendredi 2 janvier 2009 19:35
vendredi 2 janvier 2009 22:19Thank 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:
EXECUTEsp_addrolemember @rolename = N'gci_schemareader', @membername = N'$(DeveloperUser)';
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.
lundi 3 août 2009 15:53I 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.
[$(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.
lundi 3 août 2009 17:11You 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
- Proposé comme réponse Joe Moyle lundi 3 août 2009 18:47
lundi 3 août 2009 18:52Thanks 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.
[$(DI_User)] FOR LOGIN [$(DI_User)] WITH DEFAULT_SCHEMA=[dbo];
sp_addrolemember N'Data_Intercept_Role', N'$(DI_User)'
Now everything works as I desired and I get roles which I prefer over granting rights to individual users.
- Proposé comme réponse Joe Moyle lundi 3 août 2009 18:52
lundi 31 octobre 2011 21:36
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];
mercredi 18 juillet 2012 20:48
Joe, thank you, I would propose your post as answer if it wasn't already done.
This will simplify my permission handling tremendously.