Sql-server – A login is a member of db_datareader and public roles only, but it is not read-only

sql serversql-server-2008

I'm an accidental DBA in a small company that doesn't have a dedicated DBA.

I need to set up a read-only login/user for SQL Server 2008 database using SQL Server authentication that could be used by power users to run some ad-hoc queries/reports.

I found many posts and articles describing setting up a read-only user, for example:

How to grant users read-only access to all databases

https://www.itsupportguides.com/server-side-tips/sql-management-studio-how-to-create-read-only-users/

http://www.joellipman.com/articles/microsoft/sql-server/create-read-only-database-user-in-sql-server.html

I tried to follow these guides and made sure that the login in question is a member of only db_datareader and public roles, but I still can make changes to the data when I'm logged in as this "read-only" user.

For example, I can run this query in SSMS

UPDATE [dbo].[SoftwareRunLogs]
SET [RunDateTime] = '2010-10-29 13:31:06.133'
WHERE ID = 1

and it updates the row in a table:

(1 row(s) affected)

I expect such query to fail.

When I try to run a CREATE TABLE statement, it fails with the message CREATE TABLE permission denied in database ... I expect UPDATE, INSERT and DELETE statements to fail in a similar way, but it doesn't happen.

Maybe I inadvertently gave write permissions to that user, but I don't know how to check and where to look. I must have missed something trivial.

Any ideas?

There is a server login called ReadOnlyUser and here are its properties:

login property 01

login property 02

login property 03

login property 04

login property 05

In the database there is a user called ReadOnlyUser and here are its properties:

user property 01

user property 02

user property 03

user property 04

user property 05

Best Answer

I was looking through all items in Security nodes in SSMS and it seems that somehow the properties of the public database role have been changed. They looked like this:

public

I don't really know how they should look like normally, but clearing the Delete, Insert, Update checkboxes seems to do the trick.

The immediate problem is solved now and I'll have to read more about the public role and what settings it should have.

Related Question