locked
db_datareader and access to tables in non-dbo schema RRS feed

  • Question

  • We have a database with several non-dbo schemas. Typically, the owner of these schemas is the name of the schema, not dbo.

    For this DB we made a user a member of db_dataread, which supposedly:

    "Members of the db_datareader fixed database role can read all data from all user tables."

    But we see something different. Under Management Studio (2008) no tables are displayed with non-dbo schemas for that user.  In other words, SSMS only displays tables with the "dbo" schema for that user.

    Any ideas appreciated.

    TIA,

    edm2


    • Edited by edm2 Monday, July 14, 2014 5:34 PM
    Monday, July 14, 2014 5:33 PM

All replies

  • I think u need to check authorization of those members on that particular schema...

    Hopes this helps to you.

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Monday, July 14, 2014 5:56 PM
  • It is possible that the reason is similar to the root cause described in he following thread:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d46a024-7ed5-4c9b-b091-3640dc04f5a1/unable-to-set-default-schema-for-a-group?forum=sqlsecurity

    SSMS is probably issuing a query to determine the schemas & tables, but failing due to the query it executes in this attempt, which probably needs other permissions.

    I would recommend trying the latest version of SSMS, and if it still repro the problem. There has been improvements on SSMS over the last few years, and it is possible that this particular bug has been fixed.

    I hope this helps,

    -Raul Garcia

      SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, July 14, 2014 6:20 PM
  • Can you please clarify how I would "check authorization for the members of that schema"?

    Still what I find puzzling is the description of "db_datareader":

        Members of the db_datareader fixed database role can read all data from all user tables."

    It states they have should have read-only access to all tables, regardless of schema, in the DB.

    emd2

    Monday, July 14, 2014 6:22 PM

  • But we see something different. Under Management Studio (2008) no tables are displayed with non-dbo schemas for that user.  In other words, SSMS only displays tables with the "dbo" schema for that user.

     Hi., Based on the above it seems to me that you are selected default schema as "dbo". Once you have created the schema and schema based tables you should manage every permissions from schema securables only.

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Tuesday, July 15, 2014 11:00 AM
  • As per i known. If you are granting db_datareader fixed database role it can read all data from all user tables.

    You can set the permissions to schema based users from securables not from the database fixed roles.

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Tuesday, July 15, 2014 11:12 AM
  • I just checked this out in SQL 2008 R2 using SQL 2008 R2 SSMS.

    I created a new schema SQL. The owner of this schema is user SQL. I created a new login abc (SQL Authentication). I just gave db_datareader permissions for that database for this new login.

    I logged to the server using SSMS with the login abc and can see all the tables including both schemas dbo as well as sql. So you might have something else going on in your server. Or is there anything else I need to do to test this?

    Could you please check if there are any deny permissions against the schema?

    You could check that in sys.database_permissions


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, July 15, 2014 12:01 PM
  • Adding to the suggestions:

    could you run a SELECT * FROM SomeNONdbo.Table under that user?

    Does it return an error or can he see the data and only the object-list is not working?


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, July 15, 2014 1:37 PM
  • Ashwin,

    I repeated you steps and found I too could see all table names under SSMS. So I dug deeper. Here's what I found out about the mysterious schema: (suppose its name is "XYZ")

    1. "XYZ" does not exist on the server as a sql account

    2  "XYZ" exists on the server as a database role (owner: dbo)

    3. "XYZ" exists as a schema in the database (owner: XYZ)

    edm2

    BTW: I looked at sys.database_permissions but couldn't relate the result back to the schema of interest.

    Tuesday, July 15, 2014 3:59 PM
  • Andreas,

    They ran the query you mentioned and it returned "Invalid Object Name".... so the issue is more than visual.  Since, as I mentioned earlier, I found the schema name was the same as that of a database role, I added their account to the role and things started working. (I'm still confused why both a role and schema of the same name are required by the app. BTW: I saw the devs created several other roles - with no members - and identical schema names. Someone has some mighty fine talking to do!)

    edm2

    Tuesday, July 15, 2014 6:19 PM
  • Andreas,

    They ran the query you mentioned and it returned "Invalid Object Name".... so the issue is more than visual.  Since, as I mentioned earlier, I found the schema name was the same as that of a database role, I added their account to the role and things started working. (I'm still confused why both a role and schema of the same name are required by the app. BTW: I saw the devs created several other roles - with no members - and identical schema names. Someone has some mighty fine talking to do!)

    edm2

    Thanks edm2

    That's in fact strange. Using the fully qualified name it can't be a problem of "default schema"

    Just to make sure: could you do us a favor and now run the same query under your own account - assuming you have ALL permissions.

    Also you could do a select * from sys.schemas to list the schemas + respective owner id's in question


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, July 15, 2014 6:44 PM
  • I have "sa" permission on the server.  I ran the same SELECT query as the user and had no issue retrieving data from tables with non-dbo schemas.

    edm2

    Thursday, July 17, 2014 5:32 AM
  • if you dont see your schema with a deny while running sys.database_permissions against the database in question, then you wont have any denies against it..

    Could you check the user properties and confirm what it says as mapped login? I doubt if someone basically dropped the login without dropping user& schema or the database was restored from another server and the login was not copied.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, July 17, 2014 9:20 AM