locked
Granting Domain Users Read Access to Database Object RRS feed

  • Question

  • Hello,

    I wrote a client program that queries an on-premises database server within my domain network via C#-embedded SQL. My connection string uses integrated security -- all domain users have read access. I'm not an administrator but I create views and tables all the time which all domain users can query. However, in another database on a different on-premises server, my domain username is inherited in the name of any database object I create. For that reason, no one else can query those objects but me.

    If domain users have read access to the entire database before creating this view, why can't they query this view and why does it contain my username? How can I fix this?

    Thanks


    • Edited by Seif Orabi Wednesday, January 6, 2016 10:55 AM
    Wednesday, January 6, 2016 10:54 AM

Answers

  • It contains your username, because you say

    CREATE VIEW viewname AS ...

    when you should have said:

    CREATE VIEW dbo.viewname AS ...

    If you don't specify the schema, the object will be created in your default schema. In many cases this is dbo, but in the past (SQL 2000 and earlier), the default schema for a user was always the same as the username, and there still people who set up servers that way.

    The reason the users cannot query your view is of course because they don't have permission to read it. Your statement that users have read access to the entire database is apparently inaccurate.

    Wednesday, January 6, 2016 11:16 AM

All replies

  • I think the objects are getting created under your default schema in the other database which other users dont have access to. That being the case you need to GRANT them access to your schema to allow them to access the objects created in it. dbo users should still be able to access them.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 6, 2016 11:03 AM
  • It contains your username, because you say

    CREATE VIEW viewname AS ...

    when you should have said:

    CREATE VIEW dbo.viewname AS ...

    If you don't specify the schema, the object will be created in your default schema. In many cases this is dbo, but in the past (SQL 2000 and earlier), the default schema for a user was always the same as the username, and there still people who set up servers that way.

    The reason the users cannot query your view is of course because they don't have permission to read it. Your statement that users have read access to the entire database is apparently inaccurate.

    Wednesday, January 6, 2016 11:16 AM