Permissions for SQL 2005

LeeFAR

Could someone please tell me exactly what permissions are required to allow a user to perform a compare of databases in SQL 2005? Here is the problem I have. Our developers have DBO rights in their development database. However, in the testing database and production database, they do not have DBO rights. However, we need to allow them to perform compares between development, test, and production and yet still prevent them from actually making modifications to the test and production databases.

Any help you can provide would be great.

JonathanWatts

Hi Lee,

The SQL 2005 permission VIEW DEFINITION is probably what you are after to quote:

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. For more information about viewing metadata, see Metadata Visibility Configuration.

The VIEW DEFINITION permission can be granted on the following levels:

Server scope

---

it's called VIEW ANY DATABASE - Jonathan

Database scope

Schema scope

Individual entities

I have tested this as the DB level and it works, the server level might be a bit far reaching.

Hope this helps,

Jonathan

LeeFAR

Thanks Jonathan. We will look into this.

JasonKeith

I am a developer who has recently been granted VIEW DEFINITION. This works great. I can now see inside view's and stored procedures. I have used this technique to script using Red Gate SQL Compare. I still have one problem. I can't see permissions for groups I am not a part of. For example in SIT I have access to execute stored procedures. I am a member of an AD_Group called exec_SP. In UAT I do not have this permisssion (I am not in the exec_SP group). When I run Red Gate I see changes for the exec_SP group on all stored procedures. This is because I don't see the group in UAT because I am not part of it.
Are there any permissions that can be added so I can see a true picture of the database without having the power to alter objects?