sql server readonly permission

  • Which permission under sql server database properties->permission in SSMS should i select to make the database readonly for a user?

  • Assign role 'read_only' to the user on that databse

  • Add the user to the roles db_datareader and db_denydatawriter. Bear in mind that you cannot deny permissions to a sysadmin though.

    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
  • chvenkataraman (4/1/2013)


    Assign role 'read_only' to the user on that databse

    Except there is no such role...

    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
  • an example of what Gail was referring to:

    USE [SandBox] --my db for users to do stuff.

    CREATE ROLE [ReallyReadOnly]

    --give my new role READ permission to ALL tables

    EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

    --explicitly DENY access to writing

    EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

    --give my new role permission to run the procedures you've created? uncomment if true

    --GRANT EXECUTE TO [ReallyReadOnly]

    --finally, add our already exisiting user "bob" to the role we created

    EXEC sp_addrolemember N'ReallyReadOnly', N'bob'

    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!

  • Though, do note that even with those permissions such a user could, if they had the appropriate execute permissions, execute a stored procedure that deletes data. Ownership chaining (feature, not bug)

    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

Viewing 6 posts - 1 through 5 (of 5 total)

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