Post reply

Taking schema tables into consideration ,I want to create views with readonly permission

  • January 4, 2011 at 2:12 pm

    #231656

    How to make tables in a schema as views with readonly permission.

    Any help is appreciated

  • January 4, 2011 at 2:18 pm

    #1269210

    do you mean you want to create a role so that when users connect, the tables are read only?

    USE Whatever

    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]

    IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

    BEGIN

    EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

    --add this user to permit read only group

    END

    CREATE USER [bob] FOR LOGIN [bob]

    EXEC sp_addrolemember ReallyReadOnly, N'bob'

    or do you want to actually create 1000 new views that each point to an actual table (whether the same db or a different one)?

    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!

  • January 4, 2011 at 2:22 pm

    #1269213

    Thanks for your quick reply

    I want to create all those tables as views & give readonly permission.

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

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