User permissions should be separated from the stored procedures/functions/table definitions
In our set-up there are two databases, one that's used as a template and an actual working database for the application we're developing. The development takes place in the template database and then the changes are moved over to the working database (we have a staging database in which we test the changes with real data).
We would like to link the staging database to source control so that updates to it can be applied easier.
The problem is that we're using user-based authentication to allow only specific users access to specific stored procedures and SQL Source Control treats user permissions as an integral part of the stored procedures (or functions, or tables).
What we would need (and what I think would be beneficial) is for the user permissions to be decoupled from the actual stored procedure body (or table definition, etc).
Adrian Cearnau
shared this idea
-
vadim
commented
We should also implement this for SQL compare in addition to source control.
-
Pete Cousins
commented
If you're using database roles to control permissions to objects, this abstracts the permissions somewhat. However SCC includes the list of users in the role. Currently the only way around this is if the role is excluded from SCC. Personally, as a DBA, I prefer the permissions to be in SCC, as it's up to the developers to decide who should have access to what objects.
-
Anonymous
commented
If this request is executed, it should be done as an option. We currently like storing user permissions with the API objects. However, I would like the option to exclude them also.
-
mnaul
commented
This goes well with 'Option to scripted permissions under security objects' for SQL Compare.