Post reply

Minimum user permissions to query catalogue / compatibility views

  • July 1, 2005 at 10:07 am

    #165147

    So, I have these SQL Server 2000 stored procedures, many of which query information from the system tables, that I'm trying to get to run in SQL Server 2005.  However, when I try to run them, I'm running into permission issues because the user doesn't appear to have permission to run select statements against the catalogue or compatibility views.

    What's the minimum permission I need in order to be able to run a select statement against the compatibility views in SQL Server 2005 that provide backwards compatibility for the SQL Server 2000 system tables?

     

     

  • July 3, 2005 at 9:47 pm

    #570564

    What CTP are you using?  I just ran selects on the sys.system views without any trouble.  This was with a regular user.  The public role has SELECT access on those views by default.  Here is the query I ran with a newly created login(I ran it from the master database since I hadn't even created a database user yet).

    SELECT * FROM sys.syscolumns

    SELECT * FROM syscolumns

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • July 5, 2005 at 8:58 am

    #570883

    I'm using the April CTP and I do see that my queries on syscolumns work as well.  However, my queries against sys.syslockinfo do not work.  That one definitely gives me permissions errors.

    Is this part of a different category of system views?

  • July 7, 2005 at 1:33 pm

    #572066

    I'll take a crack at answering, in a rudimentary way, what I think is going on here.  I half suspect I'm wrong, but this is the best I've been able to come up with, both through research and testing.

    In SQL Server 2000, Books Online talks about 2 different levels of system tables, level 1 and 2.  Level 1 tables are the ones that contain server wide information, and level 2 database specific information.  This concept may be inherited from earlier versions of SQL Server, but I'm not sure.

    So, I created a user and granted that user db_owner to a user database in both SQL Server 2000 and SQL Server 2005.  No other privileges on system databases were granted.

    In 2000 when I run the query:

    use <userdatabase>

    go

    select * from master..syslockinfo

    it runs successfully and I get results.

    When I run the same query in 2005, I get the message:

    Msg 297, Level 16, State 1, Line 1

    The user does not have permission to perform this action

    Short of granting the server role processadmin, I've been unable to find a way to grant my user the ability to query these level 1 system views in SQL Server 2005.  Is there a new level of restriction for the public role on the master database?  Is something else at work here causing this apparent difference in functionality?  I don't know.

  • July 7, 2005 at 11:22 pm

    #572170

    I had to blow away my laptop with 2005 betas on it.    I'll try to get back to you in a couple days.  I'm not sure on the answer for this one, so I'll need to research it a little.  It would be nice if someone from MS could answer on this one.

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • July 23, 2007 at 3:35 pm

    #721710

    I ran into the same problem, and I fixed it by adding the user to the "View server state" role.

Viewing 6 posts - 1 through 5 (of 5 total)

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