sql server readonly permission

Question

Post reply

sql server readonly permission

IT researcher

SSCertifiable

Points: 7602
More actions
April 1, 2013 at 2:16 am

#272294

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

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

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

chvenkataraman Valued Member Points: 72 More actions · Answer 1

chvenkataraman

Valued Member

Points: 72

April 1, 2013 at 3:01 am

#1602399

Assign role 'read_only' to the user on that databse

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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