April 2, 2015 at 2:18 am
Hey guys, this is a bit on the obscure side but I'm having no luck researching this online.
Our Datawarehouse Team have recently upgraded their installs of SSDT to the latest version (March 2014 - 11.1.50318 - running in VS 2012) and are having a problem when they generate deployment scripts.
We use Service Broker to move data from our OTLP databases (SQL Server 2012 SP2 CU2) to a data store and since they upgraded to the new version of SSDT, the scripts generated revokes send permissions on the Service Broker services and then re-creates them. For example:-
REVOKE SEND
ON SERVICE::[SSB_Target] TO PUBLIC CASCADE;
GRANT SEND
ON SERVICE::[SSB_Target] TO PUBLIC
AS [dbo]
The problem seems to be that the account they are connecting to the database with is not the database owner. If the database owner is changed to the account they use to connect to the database and generate the script, the issue does not occur.
However, I've gone through the permissions needed for SSDT detailed here https://msdn.microsoft.com/en-us/library/jj889462(v=vs.103).aspx and it does not specify that database ownership is required.
They are a member of the db_owner role but this doesn't seem to grant the necessary permissions required. Each time the script is run, it will revoke the permissions and re-create with "AS [dbo]"
Making the account the actual database owner is not an option and they don't want to change their deployment script as this issue has only arisen since the upgrade.
This has to be a change with SSDT (nothing has changed on the db side) but there are no details about this listed https://msdn.microsoft.com/en-us/library/jj836276(v=vs.103).aspx
Has anyone else come across anything like this? If so, how did you rectify it?
I'm starting to think that this won't be solved within SSDT itself. I'll probably have to change security settings on the database side but I don't want to elevate their permissions, they already have db_owner role so I'm not sure what else to do.
Any help or advice would be most appreciated.
Thank you.
July 12, 2018 at 6:17 am
I am looking into this very situation in our environment. We are using composite projects where environment-specific security settings (users, role membership, CONNECT) are in scripts local to the related environment. In most of out database projects nearly every CONNECT permission is first REVOKEd then GRANTed. The GRANTs occur in the exact order of the Permissions.sql script in the specific environment, which implies that the security context of the deploy agent account is not able to see any permissions either because of the previous REVOKEs or because it lacks permissions to do so. (If it cannot see the permissions how does it know/decide to revoke them?) This makes RM deploy messy if not impossible.
Were you seeing this only on Service Broker-related grants or elsewhere? Did you arrive at a solution?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
July 13, 2018 at 5:31 am
This is a three year old question without an answer.
I haven't had to deal with service broker, but if I did and I was using SSDT, I'd suggest setting up manual pre/post-deployment scripts to deal with the issues described. It's some work to set them up, but it should then allow for automated deployment. Conversely, using other tools, such as TFS deployments, you could add additional steps that act as a pre/post-deployment script.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2018 at 5:53 am
Thanks, Grant. That is certainly an option. I was able to determine that the AS clause, which does not refer to dbo, was a root cause. Also I am trying to avoid making a change like that because it is a smaller issue in my environment. We are trying to tackle why we see REVOKE/GRANT patterns for other accounts as well. Since I had just deployed another Service Broker solution this caught my eye.
That said, if you have any insight in to why REVOKE/GRANT shows up regularly in our SSDT deploys, even when those deploys are not actually touching permissions. I'd love to hear from you.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
July 13, 2018 at 6:46 am
Yeah, I don't have an immediate answer to that one, sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply