I don't know of a single good comprehensive solution to this.
Local development means that developers don't break other people working on their own code against a shared database. However, when you get latest code, you also need to get the database into the right state to match the code changes you've merged in. If two people are making changes at the same time, merging can be difficult since database upgrade scripts can be incompatible. Column order doesn't normally matter in a database, but it can be a bit annoying for databases to be different.
There are good tools to compare schema and data and apply changes. I would say schemas should aim to be identical. However, typically you want lookup-type data updated between developers but not regular application data (new customer types, but not new customers). Configuration data you might want updated, BUT sometimes only a subset (new printer options but not file path settings).
You would think that ideally, you could JUST rebuild your local database completely once you've each merged changes. If you've set up a bunch of test scenarios through the application for testing (instead of in the build scripts), you now don't have a script to get those changes back into the database. And this is more difficult as the scope of the database schema increases - with surrogate keys and parent-child relationships which all might have complex dependencies.
In the ideal scenario for a central database, you would have a developer DBA managed the database interfaces for the app and control that so that the exposed interfaces would consistently evolve over time and all developers about that level would see the same interface at the same time. But then you have two separate groups coordinating their different feature timelines.
Which I think goes a long way to showing why people are still attracted to a variety of other approaches which put more emphasis on code and less on the database. Ultimately, I think that just shifts the problem around.
The question could use some clarification. SQL Server security is the same whether or not the table is empty. You have server roles, database roles, and explicit permissions. You are asking 'how can I test execute permissions on a database that has no objects to execute'.
Well the answer is you have nothing to test if the DB is empty, that should be obvious. create objects then test it, and then remove the objects. Test it on a dev box. If you're worried that you'll forget to remove all objects, backup the DB, then do your tests, and restore it. If you like, restore the DB as a different name, do all of your testing on there, then drop the newly created test DB. I'm not sure what you're asking.
Final answer remains that you cannot test permissions on objects if you have no objects to test.
I will provide a script or two at the end to help you with these steps.
Step 1: Look for users with SA rights.
What you would look for first would be SA equivelent users. Any SA user will have full access to every single object, and if they don't, they can grant it to themselves.
Step 2: Look for users with the DB_Owner role
Users granted the DB_Owner role will be able to perform any task in the database. You could DENY them access to certain features but they can just GRANT themselves access to it again.
Step 3: Audit what users are in the database
You will want to look at the 'security' settings of each user in the database. This could get very in depth as you can look for indvl permissions on each object and this would be required if you were taking over a database. In your case, there are no objects and you get to create them yourself, thus we don't have any object level permissions to go through. You will want to check what roles each user is in. DataReader will be able to read anything in the DB unless explicitly blocked. DataWriter will be able to write anything. Execute role lets them execute sprocs.
Step 4: Continue to monitor after objects are created
Now comes the hard part. What happens when you go live and a sysadmin grants themselves rights to the machine/db? What if you have the option to have start up stored procs execute? If you're on 2008R2 and below, the NTAUTHORITY user can always be used as a backdoor. This is where real security auditing and security policies are needed but luckily it's out of scope of this question.
Check out:
http://www.mssqltips.com/sqlservertip/1881/sql-server-security-audit-report/
His script will cover almost everything you're looking for.
Also this one is very good too, but test it out first:
http://gallery.technet.microsoft.com/scriptcenter/SQL-SERVER-SECURITY-AUDIT-eb5b0fd5/view/Discussions
If you want to do some research on your own, search for "SQL Server Auditing" or "Object Level Auditing SQL Server".
Does this info help? Do you have any follow up questions?
Follow up edit to address permissions under the current user context:
In that case you'll want to use this:
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
USE DBName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
The first one will tell you your permissions on the server, the 2nd one will tell you your permissions on the database.
Click here for more easy to read info.
Best Answer
I would recommend the following approach:
a) Each developer has their own database that they can work with, try out different things, destroy and restore at will independent of the other team members.
b) Have a shared database instance for integration testing so that the whole team can see the shared progress at any one time
c) Use windows authentication to speed up the connection process, and the devs do not have to remember new passwords, and easier to track and audit
Of course all this depends on the size of the database and what resources are available on the test server.