Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql server readonly permission Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 2:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 21, 2014 10:47 PM
Points: 269, Visits: 1,732
Which permission under sql server database properties->permission in SSMS should i select to make the database readonly for a user?
Post #1437377
Posted Monday, April 1, 2013 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 6:42 AM
Points: 2, Visits: 20
Assign role 'read_only' to the user on that databse
Post #1437382
Posted Monday, April 1, 2013 3:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1437385
Posted Monday, April 1, 2013 3:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1437386
Posted Monday, April 1, 2013 6:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 12,903, Visits: 32,144
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437410
Posted Monday, April 1, 2013 6:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1437418
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse