Post reply

Permission Denied on "sysobjects"

  • July 16, 2004 at 1:10 pm

    #85890

    I am guessing than this may be more elementary that I am making it out to be.  Here goes...

    We are getting an error message when our test user tries to use SQL Query Analyzer to access a new database.  The error we are getting is as follows, "[Microsoft][ODBC SQL Server Driver][SQL Server] SELECT permission denied on object 'sysobjects', database '<database_name>', owner 'dbo'."  In this sample of the error I replaced the true DB name with "<database_name>".

    Anyway, we have setup up a ROLE that has been given "SELECT" permission to sysobjects along with many other objects.  In the ROLE, we have added 3 USERS.  Our test user's SQL Login ID has been added to each of these 3 USERS.  Our test user has also been given specific permission to the database in question.  None of these 3 USERS have been denied any access to the objects in this given database.

    What am I missing?  What do I need to look at in order to allow our test user access to "SELECT" on sysobjects?

    Thank you,

    Todd R. Thomsen

  • July 16, 2004 at 1:33 pm

    #514937

    Check the permissions of 'Public' role which may be denied to access sysobjects table.

  • July 16, 2004 at 1:37 pm

    #514938

    Good thought, but PUBLIC has not been denied access to anything. and has been given SELECT access to sysobjects.

    Thank you,

    Todd R. Thomsen

  • July 16, 2004 at 1:40 pm

    #514939

    Trying check the permissions on the object using Enterprise manager, and see if anyone is denied access on the table

    Steven

  • July 16, 2004 at 1:43 pm

    #514940

    Thanks for the idea, but no one has been denied access to sysobjects in this database.

    Todd R. Thomsen

  • July 16, 2004 at 2:31 pm

    #514949

    I have seen similar messages under one of the following conditions:

    1. Users were accessing the database through the view in another database The permissions were denied after applying SQL 2000 SP3 that introduces "allow cross-database ownership chaining" and selecting defaults during SP3 install (don't select cross-database ownership chaining, recommended)  A fix was to check this checkbox.

    2. Users are in the incorrect database. Database name is not specified in ODBC source or connection string and the default database is used.

    3. Everything was checked for the user or role in the database access tab including 2 entries at the botom of the list: db_denydatareader and db_denydatawriter

    4. Use Profiler or logic to figure out what login is actually trying to connect

    It is not that simple. I was running OPENROWSET and was able to connect using my Windows credentials from the local server to another server. But I lost my credentials and was denied access if I used Query Analyzer on my workstation, connected it to the server and ran OPENROWSET to connect to another server.

     

    Regards,

    Yelena

    Regards,Yelena Varsha

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

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