Rights for non-sysadmin role to view table columns

  • Is there any way to allow a user to use SQL Query Analyzer and/or Server Explorer in Visual Studio .Net and view a tables columns without being in the sysadmin role?

    The error message says SELECT permission denied on object syscolumns and systypes. Giving SELECT permission to these database-specific system tables does not resolve the issue.

  • make sure the corresponding database user isn't a member of the fixed database role db_denydatareader within the database you are trying to view.

    ll

  • The answer is yes. After seeing the post on public role, I looked at the SELECT permissions that the public role had on systypes and syscolumns and they had been explicitly denied on every one of our servers. I checked on my laptop where I had recently installed several sample databases and the public role had all been explicitly (green check mark) granted to the public role.

    So, it was an issue unique to my environment where someone somewhere modified the default public role permissions.

    I explicitly re-granted SELECT permission on systypes and syscolumns for my database and removed myself from the sysadmin role and I was able to view the columnar information for the database. So, yes, a developer does not necessarily need to be a sysadmin on the database. I personally prefer to use the least amount of privileges and only use sysadmin when absolutely required.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply