Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2010
    Posts
    25

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

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

    Any help is appreciated

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    deny insert,update,delete on schema::<your_schema> to <user_or_group>
    grant select on schema::<your_schema> to <user_or_group>
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Aug 2010
    Posts
    25
    I want to create all those tables as views & give readonly permission.

    Thanks for your quick reply

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by capvenu View Post
    I want to create all those tables as views & give readonly permission.
    rdjabarov got exactly the permissions that you wanted without the need to create the views.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2010
    Posts
    25
    But the requirement is need to create views.

    Thanks for your reply

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What do you mean "the requirement"??? There is a business need, which is "what". Your job is to provide the answer for "how". The requirement, which is "what", which is a business need, - cannot have the "how" part. It may have a "how much" part, but that will not require views
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Aug 2010
    Posts
    25
    MY Boss requirement

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How much data is expected to be stored in those views, and what kind of data? You can create a view like:
    Code:
    create view dbo.myView as
       select Field1 = 1, Field2 = 'ABC' union all
       select 2, 'EFG'
    go
    But what is this kind of view for?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Aug 2010
    Posts
    25
    Suppose i have schema named as 'XXX' having 50 tables in it & i want these
    tables to be created as views so that readonly user will have only read access to those views.

    Thanks for your reply

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So you DO have tables? And you want to create views? And you want them read-only?
    Code:
    select
       'create view xxx.[read_only_view_' + name + '] as select * from [' +
       schema_name(schema_id) + '].[' + name + '] union all select * from [' +
       schema_name(schema_id) + '].[' + name + '] where 1=2
    go'
       from sys.tables
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Aug 2010
    Posts
    25
    Thanks RD.Appreciate it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •