how to set up user READ only permission 1

[bloomlight]

I have an user who would need READ only permission. The user would only need to access database and select data for reporting purpose. How to set up that?

I gave the user "public & db_datareader" permissions. But when I login as the user, I could still get into table area. Will the user be able to do anything with the tables? I wanted to make sure that nothing would happen to the tables. Please help!!!

 

[fuzzyocelot]

Giving the user the db_datareader permission should only allow them to read the data in the tables in that specific database. They can't change the data or the table structures.

If you want to give them rights to specific tables only, you can grant them the select permission per table instead of the db_datareader permission.

 

[tadd]

I tried this...and I am totally befuddled, because my user can still change data in one of the the tables. What could I be missing? Do I need to bounce the server?

I am certain of her identity when she logs in because I can see the login event in the SQL log. And I have associated only two Roles to her User account: public and db_datareader. Still she can edit fields in tables in the database.

Oh, and no specific table/column permissions have been set configured for her.

Anybody have any ideas?

 

[mrdenny]

Is she a member of the Windows Administrators group?

Has anyone added permissions to the public role? If anyone has setup update rights on the public role everyone in the database will be able to make changes.

Is her SQL Login a member of any server roles?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tadd]

Here is the security config for her so far:

SERVER LOGIN
- I have created a Server Login for her, and the type is a Windows User (eg: OURDOMAIN\Sue.Jones)
- She has not been associated with *any* of the SQL Server Roles.

DATABASE USER
- A User was created for her for the database in question.
- This User uses the Server Login mentioned above.
- This User is associated with the following Roles.
- public
- db_datareader
- db_denydatawriter

Now...yes...update rights have been issed to the public role on just about all of the tables. But...what is weird is that...the one table we were testing with does *not* allow editing by the public role. Yet she was able to edit data in this table.

When you ask "Is she a member of the Windows Administrators group?" are you asking if she is an administrator on our Windows domain?

I have a question: If a user is assigned to the public role, and the public role allows editing of a certain table, does that override also belonging to the db_denydatawriter role? In other words, does the most restrictive right take precedence, or is it the other way around?

 

[tadd]

I should clarify one more thing. When I say she is able to edit data in the table, I mean she is opening up EM, opening a table and editing a field in a row of data. The change keeps.

 

[mrdenny]

Is she a windows Administrator on the domain or on the SQL Server should have been my question.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

After she makes the change in Enterprise Manager when she closes the table and reopens it is her change still there, or has it reverted back?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tadd]

She does not have any administrative rights on our domain and her SQL Login is not associated with *any* Server Roles.

 

[tadd]

OK, we tried editing a row from EM, and then closing the table and reopening - and the change was still there.

I'm going to try starting from scratch - creating a new user and see what happens. Maybe there is something hidden in this user's settings somewhere that I just can't see...

Any other pointers are greatly appreciated...

 

[mrdenny]

Not the SQL server roles. The Local groups at the OS level of the machine running the SQL Server.

sp_denydatawriter should overwride all permissions unless she in inhearting admin rights from somewhere.

Also make sure that she's in the db_denydatawriter not the db_datawriter role. I've put people into the wrong one before by clicking to fast.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tadd]

Also, I just created a new Server Login and Database User, and gave it the same rights as "Sue.Jones" (refer to previous post) and the new user was not able to edit data. There is something different about "Sue.Jones" that I am missing. Could it have something to do with her logging into the server using Windows Authentication? My new user is just a regular old SQL user (not member of our Domain)

 

[tadd]

MrDenny, I think you hit the nail on the head. I just checked, and yes, she is a member of the Administrators group on the machine on which the SQL server is running.

So basically, you're saying that overrides any security settings in SQL Server?

 

[mrdenny]

No it doesn't overwride it. By default the Windows Group Administrators is a member of the sysadmin server role. Being a member of the sysadmin server role overwrides any deny rights that you have.

At this point you have three options.
1. Remove her from the Windows Administrators groups.
2. Remove the Windows Administrators group from the sysadmin role (testing will probably need to happen to do this).
3. Do nothing and leave her with write access to the databases.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tadd]

Thanks mrdenny. Big help.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bloomlight]

The user who has the READ only permission also needs to use SQL Query Analyzer to run reports. He is OK to run most of reports. But when he trying to "create view", he got error message saying "CREATE VIEW permission denied in database 'xxxxxx'.

What permission does he need to be able to create view?
I know that sysadmin role may allow him to do that. But at this point, he couldn't have sysadmin role permission.

Any suggestions would be greatly appreciated.

 

[SQLWilts]

Use MyDB
GRANT CREATE VIEW TO MYUSER

 

[bloomlight]

Thanks SQLWilts.

What is MyDB? We are using Microsoft SQL 2000 server. Is MyDB part of SQL2000 server?

How can I grant "Create View" to the user? Thanks again.

 

[SQLWilts]

MyDB is the name of the database that you want to give the user permissions on, and MYUSER is the login of the to whom you want to grant permissions