0

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

3
  • Did you check sys.database_permissions? (These don't show up in point-and-click GUIs because you could literally have 2 billion columns each with multiple individual granular permissions for a million users. Trying to get that to draw on one of these hokey dialogs you're using would be impossible.) – Aaron Bertrand Mar 16 '16 at 2:41
  • (Also, you don't have to include screen shots of things with zero information, like extended properties. They're not relevant, and we will probably take your word for it if you tell us that a screen shows up blank.) – Aaron Bertrand Mar 16 '16 at 3:12
  • Thank you @AaronBertrand for the hint. I'll read more about sys.database_permissions. Meanwhile, I think I found in the GUI the settings that were incorrect and added it as an answer. I have no idea how permissions in the database ended up in that state, but it is a different story. – Vladimir Baranov Mar 16 '16 at 3:46
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.