Server role

Question

Hello All,

I just created two windows active directory groups.One for assigning users with full permissions that is sysadmin role and the other group just need limited access like only reading permissions.They are not going to update anything in the server or database.I am really new to administrative side.Could anyone please tell me what is the best server role I can give for non sql users (users with limited permissions) When I am creating the login ?..

Thanks

Answer 1

You can  use db_datareader built in role.

Answer 2

In the server role section I cant see db_datareader role.I can only see

bulkadmin

dbcreator

diskadmin

sysadmin

processadmin

securityadmin

serveradmin

setupadmin

Answer 3

the group with reading permission only do have server role public and database role db_datareader.

in another thread about readonly access via Access it was recommand to set db_denydatawriter to enforce readonly access.

see this link for description of database roles: Database-Level Roles

and description for serverroles: Server-Level Roles

Answer 4

But I cant see public role in server roles section.Please help.

Thanks

Answer 5

if no Server-level roles is selected than it gets automatically public role.

uncheck all server level roles

Answer 6

You don't see it on the list because the db_datareader role is a database role, not a server role. This should be fine if the databases on the SQL Server are always the same. (Just assign the database role permission in the databases you want.) If new databases are regularly being created, you may need to add the login membership to the database role in the model database, which is copied to create new databases.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 7

I have created 2 windows NT groups. One group has members and those should have sysadmin privileges and the other group members should just have read permissions.So for the Read only group I just checked db_denydatawriter in database role section and I didn't check anything in server role section so it is public. But when I tested it they can actually create tables in the server which I  didn't expect.What I expected is they should get an error saying that they dont have permission to create a table. Please correct me if I am doing anything wrong.

Thanks for any help.

Answer 8

Windows users can get access to SQL Server through logins in their own name, or by membership in one or more Windows groups. (I believe you understand that.) The effective permissions of the person is the aggregation of permissions granted through any method. Unless explicitly denied. So... I guess that some of your users are members in more than one Windows group, and even though one of their groups has only read-only permission, some other group (that they are a member of) has more permissions. Are there lots of logins that represent Windows groups?

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 9

see this article Understanding Execution Context to understand how a you may get write access even if you thought having disabled it.

Ask the user to run the following T-SQL statement to get the token assigned to him

SELECT principal_id, sid, name, type, usage FROM sys.login_token;
SELECT principal_id, sid, name, type, usage FROM sys.user_token;

or try this code

select  slt.name, ssp.type_desc
from    sys.login_token slt JOIN sys.server_principals ssp ON slt.sid = ssp.sid
where   ssp.type_desc = 'WINDOWS_GROUP'

from this article Finding All Access Groups For A Windows Login With sys.login_token

as soon you know the group membership of the user you can check which give him write access.

Older version of SQL Server automatically give sysadmin privilege to all local administrations by Builtin\Administrator group.

Answer 10

As you need to give limited access for read purpose only then you need to give db_datareader at database level for each database that you need your login/user need to have access to. This will help you to control permission as database level. You can use following t-sql with replacing the specifics:

USE <Database_Name>
GO
CREATE USER <LoginName>;
EXEC sp_addrolemember 'db_datareader', <LoginName>;

Hope that helps.