May 7, 2013 at 11:02 am
We have a number of databases that are now encrypted. Someone deployed a release to one of them and the deployment settings set encryption off. Now... In order to do the deployment, the deployment user has to have db_owner privileges. However, we want to block it from succeeding with this again if the settings "forget" to be changed. I am not as good at security as I should be, so any thoughts appreciated.
Jared
CE - Microsoft
May 7, 2013 at 1:53 pm
You can try deny CONTROL permission on the database or VIEW DEFINITION permission to role.
not sure, whether will work with db_owner or not.
May 7, 2013 at 3:10 pm
Trying to deny permissions to db_owner would effectively invalidate any reason to have the user in db_owner.
The best thing to do would be to ensure there is a review process such that the DBA team reviews all deployments and ensures the settings do not get changed (or if they do for the deployment, then they get returned).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 8, 2013 at 5:37 am
SQLRNNR (5/7/2013)
Trying to deny permissions to db_owner would effectively invalidate any reason to have the user in db_owner.The best thing to do would be to ensure there is a review process such that the DBA team reviews all deployments and ensures the settings do not get changed (or if they do for the deployment, then they get returned).
Darn you for making sense! 🙂 Unfortunately, I think that is the case. It is quite strange that there would be a setting to decrypt the database during deployment. Even moreso that it is defaulted to decrypt. Thanks!
Jared
CE - Microsoft
May 8, 2013 at 9:21 am
What are the exact tasks required for the deployments?
What is needed beyond what the combo of db_ddladmin, db_datareader, db_datarwriter, db_securityadmin and db_backupoperator ?
Please note, that I am not sure if any of these roles give the ability to turn off encryption.
Still, I would start with trying to use some combination of roles that do not grant full dbo.
Also you probably should have monitoring that tells you that encryption has been turned off.
May 8, 2013 at 9:46 am
I believe that TFS actually checks to see if the user is a member of db_owner, if it is not... It will not deploy. Whether or not there are settings to change this, I don't know. Will look into it. However, it seems that MS is assuming that if someone is deploying something to a production environment that they know what they are doing.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply