What db rights are needed?

yzguy

I recently installed SQL source control, and it looks like it will do exactly what we need. But so far I have run into a snag trying to check in changes.

We work with a shared dev db (multiple developers working on the same db & server), and am trying to check in changes to TFS. I got the differences, and selected one, then clicked commit. Then I get this error message:

An error has occurred
You may need to ask your sysadmin for more permissions.
X Checking extended property permissions failed


I assume this means that I don’t have enough rights on our development db server to do something? Is there someplace that has a list of all the rights we may need/want on the db server to use sql source control? (or is this error because of rights in TFS?)

Manfred.Castro

SQL Source Control uses the SQL Compare engine so the permissions required are equivalent to those needed for SQL Compare.
Here is a document that describes the permissions required for SQL Compare.
http://www.red-gate.com/supportcenter/c ... s_Required

yzguy

I have been using sql compare for quite some time, and can use that just fine. There seems to be some rights issue I'm having, but I'm not sure exactly what sql source control is doing so I'm not sure what rights to ask for. For that matter, I'm not even 100% sure it is db rights, it could be TFS rights, but judging by the list of things it says it is doing, it looks to be all db related stuff until the end (and this is the very first thing it is trying to do).

I can probably get the rights, I just don't know what to ask for.

BTW, the compare and the list of items to check in works fine (which is what I would suspect to be similar to sql compare) it isthe commit changes that is failing.

yzguy

in case anyone else needs to know, this statement needs to return 1 for the user trying to commit changes

SELECT HAS_PERMS_BY_NAME('YourDBName', 'DATABASE', 'ALTER')

rexyvanderpool

I am having the same issue with a SQL Login/user I created to mimic the setup that a fwe developers are going to be using to work on a shred database.

It works when I give this Test login the db_owner role, but I don't want the developers to have that in practice because we are not version controlling database users and I am concerned that they wil accidentally check in a user when checking in a proc (since SQL Source control prompts you to checkin users that have explicit rights to an object, as we do) The script that gets generated by SQL Source control when checking in a user has "create Login" code in it, which is a big potential security issue if developers can create a SQL Login/PW at the server level and get it deployed to other environments via checking in a database user.

I suppose another way to solve this problem would be if there is a way to suppress the Create Login part of the script when checking in a user, is that possible?

carpentercraig

I'm not sure I'm following this thread either -- why would I need ALTER permission on the database, when the commit changes button is documented as committing changes to the source code repository (SVN, TFS, etc). What is being altered in the database?

tim_harkin

When changes are checked in to SSC, it actually writes extended properties on the database. That requires db_owner rights.