Hello there. i have generated a Dacpac file with data from our production DB. Now I want bring the dacpac back to our dev environment.
When publishing I have the following error:
Error SQL72014: .Net SqlClient Data Provider: Msg 15401, Level 16, State 1, Line 1 Windows NT user or group 'pmclp\ADeGuzman' not found. Check the name again.
Error SQL72045: Script execution error. The executed script:
CREATE USER [pmclp\ADeGuzman] WITHOUT LOGIN;
The error is obvious to me, I dont have the login. The user that the system is looking is not even belong to our Active Directory.
How can exclude the login's and users (that not exist) from the dacpac when publishing to our Dev environment?
And second how can publish with Data (I have created the DacPac with data)?
Thank you in advance.
Instead of excluding, you will have the think opposite. A dacpac is a blue print of how you want your target to look like. We therefore exclude logins and users from the deployment and manage them in the post deployment. Objects are owned by schemas, rights are granted to roles. In the post-deployment we add logins and users and map them to roles.
Regarding data deployment. See http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx or you might be able to use the SqlPackage command line ability
Indicates the table from which data will be extracted. Specify the table name with or without the brackets
surrounding the name parts in the following format: schema_name.table_identifier.
But note that this is rip-replace and no incremental data deployment. You could make in to that with some work, if you want to use it to deploy reference tables, but then you deploy to a staging table and do the incremental merge from withing a post-deployment script.
-GertD @ www.sqlproj.com
Thank you GertD.
I have had some progress on this issue may be this will help others.
With the release of SSDT June release you now can compare data and include data into your data tier application file(dacpac).
When using SSDT to extract dacpac from the Production DB with data, I have chosen to ignore the user:
This action in theory should exclude the Login's, permission and etc. and the mappings. But in reality it is not.
If you do the opposite and publish the dacpac to a database (or even generate one from the SQL server project) and choose to ignore permissions user settings and user login's in the advance settings. The SSDT will not publish the user and will not cause the problems.
I think this is a bug in the SSDT when extracting the dacpac from the DB.
Other then this the tool works fine. you can manipulate the users in the post deployment script or directly in the SSMS the data transfer is superb and easy to perform.
So my question is still open how to exclude user when generating the dacpac from the DB, since publishing to DB and ignoring the users is working.
He means that you deploy from your project to production. And not from production to the project.
I had a problem that i needed to bring back the DB from production to to our development environment. But in th process the DACPAC grabbed the user as well. And when creating the DB on our local system, it complained that the logins does not exist.
When you deploy to a production, you can just uncheck the users and logins in the setting window for the dacpac.