| Author |
Topic |
|
Mazeppa
Starting Member
5 Posts |
 Posted - 2011-06-29 : 16:13:39
|
| Our proprietary database instance (5 databases), which is installed at customer sites, is completely locked down, allowing no Windows Authenticated users, and only the access required for the logins that our system uses. Only developers and the highest level of tech support know the sa password. Our product (it's an entire system) can create and modify tables and views in our databases when it runs. Now our installation engineers swear that they must be able to view the database schema, which is the same for every "clean" database but will grow to be different for every customer. Does anyone know how to create a login that can view any data or any schema, but can't modify any data or any schema? Sure, I could create one, by assigning explicit permissions to each of the thousands of objects in our databases, but that would be a maintenance nightmare. We would also have to add code to keep up with this as our system modifies the schema. I don't really care if the answer is "it's impossible", I just need a definitive answer for this so people will quit asking for it.Thanks,Mazeppa |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-29 : 17:03:22
|
quote:
Only developers and the highest level of tech support know the sa password
NO ONE should know the sa password, especially developers. If you must have a system admin login, create your own with a different name and your own password policy, but do not co-opt sa for your own use.As far as "maintenance nightmare", the best thing to do is add permissions to a database role, then add the relevant users to that role. Role-based permissions are much easier to manage than users, since you only need to change permissions one time. |
 |
|
|
Mazeppa
Starting Member
5 Posts |
Posted - 2011-06-29 : 17:17:42
|
| Actually, I already have some read-only logins that are used by code. (The logins aren't read-only, but they are associated with users that are members of roles that have read-only permissions.) And anyone can use them to log in and query for anything. However, when logged with as one of those users, you are unable to open the Databases node in Object Explorer. How can I add permissions so that such a user can browse the Databases tree? |
|
|
|
Mazeppa
Starting Member
5 Posts |
Posted - 2011-06-29 : 17:20:01
|
| Correction: you are able to open the Databases node, but our databases do not appear in there. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-29 : 17:20:46
|
| I believe the VIEW DEFINITION permission should do the trick, but I haven't tested it. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-29 : 17:24:19
|
| Also look at the CONNECT and VIEW ANY DATABASE permissions. |
|
|
|
Mazeppa
Starting Member
5 Posts |
Posted - 2011-06-29 : 19:21:30
|
| VIEW ANY DEFINITION did the trick!Thank you, thank you! |
|
|
|
Mazeppa
Starting Member
5 Posts |
Posted - 2011-07-06 : 11:12:31
|
| Well, I spoke a little too soon. VIEW ANY DEFINITION works if the version of SSMS is 10.50.1617.0. If the version is 10.50.1600.*, it does NOT work. The 1600 version is the one that comes with SQL Server Express, and it's the version that we must use.:( |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-06 : 11:25:40
|
| You can try applying a cumulative update to your Management Studio machine: http://support.microsoft.com/kb/2534352If that doesn't work, you may also have to patch the machine with the Database Engine components, but that shouldn't be necessary to fix a viewing problem in SSMS. |
|
|
|
|
View-only login in SSMS