April 1, 2013 at 2:16 am
Which permission under sql server database properties->permission in SSMS should i select to make the database readonly for a user?
April 1, 2013 at 3:01 am
Assign role 'read_only' to the user on that databse
April 1, 2013 at 3:41 am
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
April 1, 2013 at 3:41 am
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
April 1, 2013 at 6:10 am
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
April 1, 2013 at 6:26 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy