Ignore Sql Logins feature

Question

Hi guys, one things that I discovered the other day is that there is no deploy option available to tell the deploy engine to "ignore SQL Logins" and I can see that this is probably going to be required

Often in environments I have worked in (and considered best practice) developers do not have access to view or create server level security settings. They may have access to create databases (which is all they should need) but the deployment engine currently will check the logins, find there aren't any whether they exist or not (due to the lack of permission to see them), try to create them and then fail.

Is there a reason why this option doesn't exist and if not could it be made part of the release version please? Thanks

Answer 1

Hi Brett,

We corrently do not provide any option to suppress deployment of any object defined in the project.  We provide options to ignore properties in comparison or drop objects that do not exist as well as numerous options to influence the deployment engine behavior.  Schema Compare allows you to suppress objects by type.

If logins are not defined in the project the deployment engine will not attempt to deploy them.

We do have an improvement that will be available in the next refresh of the bits that makes the insufficient permissions case more resilient.

Generally our guidance is to not maintain logins, users, permissions, and role membership through the project system if targeting mulitiple environments.  Logins and users are often specific to environments especially with windows logins where the doman is different acorss environments.

There are options to ignore referenced logins when populating a project from a database.

Does this help?

Thanks,

---

Thanks, Barclay Hill Sr. Program Manager, SQL Server – DMG (Juneau) | Microsoft Corporation http://blogs.msdn.com/bahill Please mark the responses as the answer if it resolves your question/issue.

Answer 2

Thanks for the reply Barclay.

I understand your suggestion about not including the user/login information however there is quite a lot of useful and important configuration here for applications to actually work. I have managed differring domains in the past by using SqlCmd variables for the domain part which worked well and this kept Dev/UAT and Live good representations of each other. Too often can permissions be the cause of a failed live deployment.

My point is that there could be 3 patterns of deployment relating to security objects/settings

1.No permissions (your suggestion, no server level permissions required)
2.All Permissions (including Logins requiring server level security permissions)
3.All except Login Permissions (what I am suggesting here, no server level permissions required)

(rather than the 2 that you seem to be suggesting: types 1 and 2):

With your suggestion of not including any (type 1)  what would happen if I wanted to use the option "drop objects if don't exist" if I didn't include the users would this then remove them?

The VS2010 database projects I thought did have this functionality to remove specific object types from deploy and I think that a certain amount of this would be useful in these tools.

Anyway, just some feedback to consider.