locked
Can we change access rights of a user on a Table of DB RRS feed

  • Question

  • User283528319 posted

    hi all,

    Can we change a user's access right to "only read" on an MsSQL table?

    or it is obligate to use schemas for this aim 

    I meant do you say "Why do schemas exist"

    Thursday, August 1, 2019 12:15 PM

Answers

All replies

  • User475983607 posted

    fatihbarut

    Can we change a user's access right to "only read" on an MsSQL table?

    Yes, set the user's role to db_datareader.

    The SQL security documentation covers this topic quite well.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017

    fatihbarut

    or it is obligate to use schemas for this aim 

    I meant do you say "Why do schemas exist"

    A schema is not required but a schema will make it easier to include or exclude a group of database items.  See your similar thread from today.

    https://forums.asp.net/t/2158387.aspx?Is+there+a+benefit+to+separate+a+DB+to+Schemas+performance+wise+if+you+use+EF+core

    Thursday, August 1, 2019 1:21 PM
  • User283528319 posted

    db_datareader.

    no, I didn't mean that I want user to change some tables but only read some tables.

    Thursday, August 1, 2019 1:47 PM
  • User475983607 posted

    no, I didn't mean that I want user to change some tables but only read some tables.

    Similar to your other threads, you're making wild assumptions and not reading the linked documentation.

    db_datareader is a role in SQL server.  From the link above...

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

    Set the user's role db_datareader only and the user can only read table data.

    Thursday, August 1, 2019 2:00 PM
  • User283528319 posted

    Similar to your other threads, you're making wild assumptions and not reading the linked documentation.

    no, actually I am right this time I need specific permission to a spesific table not all the tables in db. really...

    Thursday, August 1, 2019 2:04 PM
  • User475983607 posted

    fatihbarut

    no, actually I am right this time I need specific permission to a spesific table not all the tables in db. really...

    I did not understand your question.

    Anyway, this is where schemas come into play.  Grant the user select access to the schema.

    CREATE USER myUser FOR LOGIN myUser WITH DEFAULT_SCHEMA = mySchema
    go
    
    GRANT SELECT ON SCHEMA::mySchema TO myUser
    go

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 1, 2019 2:22 PM