Post reply

Read only access to view all objects in a DB

  • June 21, 2010 at 9:43 am

    #387192

    I need to give a user read-only access to a database, but they need to be able to see and view all objects. In other words, I want them to be able to look at anything in the db--data, objects or the mess in the kitchen. I just don't want them to touch anything. They now have access to see the data, but I don't know how to let them see the objects in read-only mode. Ideas?

  • June 21, 2010 at 9:50 am

    #1182816

    something like this is what you want;

    the permissions is VIEW ANY DEFINITION, which is what you were asking for: let them see the text of any view/proc/function.

    CREATE ROLE [ReallyReadOnly]

    --give reader writes to this group

    ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReallyReadOnly]

    --explicitly DENY access to writing

    ALTER AUTHORIZATION ON SCHEMA::[DB_DenyDataWriter] TO [ReallyReadOnly]

    --can the users EXECUTE procedures? uncomment if true

    --GRANT EXECUTE TO [ReallyReadOnly]

    --allow the users to see view proc and function definitions

    Grant View Any Definition To [ReallyReadOnly]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 21, 2010 at 9:55 am

    #1182822

    Give them view definition rights on either the object or the schema.

    GRANT VIEW DEFINITION ON schema::<schema name> TO <user>

    I think that's the syntax...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 21, 2010 at 10:15 am

    #1182835

    Thanks Mucho!!

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

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