locked
how to grant a member of a group extra permission RRS feed

  • Question

  • the group 'NT AUTHORITY\Authenticated Users' only has write permission (db_datawriter) for a database. How can i grant one member of this group read  (db_datareader)  and write permission  (db_datawriter) for that database?

    Wednesday, July 20, 2011 11:24 PM

Answers

  • Hi,

    Create individual login for that member ,map him to a database ,and include him into desired database roles.

     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Peja Tao Friday, July 22, 2011 8:24 AM
    • Marked as answer by Peja Tao Wednesday, July 27, 2011 7:03 AM
    Thursday, July 21, 2011 12:29 AM
  • What Siva siad is correct, and you can look at this script for a demo. Here
    I use a loginless user and a database role for simplicity, but you can tried
    with your Windows group instead.

    It seems that there is something more in this database that you need to look
    for.

    [sql]
    CREATE DATABASE ture
    go
    USE ture
    go
    CREATE TABLE tbl (a int NOT NULL)
    go
    CREATE ROLE rolle
    EXEC sp_addrolemember db_datawriter, rolle
    CREATE USER uslig WITHOUT LOGIN
    EXEC sp_addrolemember rolle, uslig
    go
    EXECUTE AS USER = 'uslig'
    go
    SELECT a FROM tbl -- Fails
    go
    REVERT
    go
    EXEC sp_addrolemember db_datareader, uslig
    go
    EXECUTE AS USER = 'uslig'
    go
    SELECT a FROM tbl -- Now it works
    go
    REVERT
    go
    USE tempdb
    go
    DROP DATABASE ture
    {/sql]


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Friday, July 22, 2011 8:24 AM
    • Marked as answer by Peja Tao Wednesday, July 27, 2011 7:03 AM
    Thursday, July 21, 2011 9:14 AM
  • No wonder it works for temp_common, since this user has db_owner rights.

    But your original question was how add a member of "NT AUTHORITY\Authenticated Users" to db_datareader, and you were told to create a login for that member and add that login to db_datareader, and you told us that this did not work.

    However, I cannot see this login in this listing, so it seems that you have simply failed to add him to db_datareader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Monday, July 25, 2011 12:57 AM
    • Marked as answer by Peja Tao Wednesday, July 27, 2011 7:03 AM
    Saturday, July 23, 2011 8:39 AM

All replies

  • Hi,

    Create individual login for that member ,map him to a database ,and include him into desired database roles.

     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Peja Tao Friday, July 22, 2011 8:24 AM
    • Marked as answer by Peja Tao Wednesday, July 27, 2011 7:03 AM
    Thursday, July 21, 2011 12:29 AM
  • It still does't work, i created a new login for that member, map into the database, grant db_datareader and db_datawriter to him. but he still can't read the data.  he can read data if i add db_datareader to 'NT AUTHORITY\Authenticated Users'

     

    Thursday, July 21, 2011 2:42 AM
  • What Siva siad is correct, and you can look at this script for a demo. Here
    I use a loginless user and a database role for simplicity, but you can tried
    with your Windows group instead.

    It seems that there is something more in this database that you need to look
    for.

    [sql]
    CREATE DATABASE ture
    go
    USE ture
    go
    CREATE TABLE tbl (a int NOT NULL)
    go
    CREATE ROLE rolle
    EXEC sp_addrolemember db_datawriter, rolle
    CREATE USER uslig WITHOUT LOGIN
    EXEC sp_addrolemember rolle, uslig
    go
    EXECUTE AS USER = 'uslig'
    go
    SELECT a FROM tbl -- Fails
    go
    REVERT
    go
    EXEC sp_addrolemember db_datareader, uslig
    go
    EXECUTE AS USER = 'uslig'
    go
    SELECT a FROM tbl -- Now it works
    go
    REVERT
    go
    USE tempdb
    go
    DROP DATABASE ture
    {/sql]


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Friday, July 22, 2011 8:24 AM
    • Marked as answer by Peja Tao Wednesday, July 27, 2011 7:03 AM
    Thursday, July 21, 2011 9:14 AM
  • the error I got is:

    The SELECT permission was denied on the object 'Survey', database 'SurveyTest', schema 'dbo'.

    When I run the following script, there is no problem.

    use surveytest
    grant select on survey to temp_common
    execute as user = 'temp_common'
    select * from survey

    Friday, July 22, 2011 9:14 PM
  • Exactly when did you get this error? In your original attempt? When you ran my script? You will have to execuse, but it is difficult to help with incomplete information.

    Anyway, please run the this SELECT, and post the output:

    SELECT m.name, r.name
    FROM   sys.database_principals m
    JOIN   sys.database_role_members rm ON m.principal_id = rm.member_principal_id
    JOIN   sys.database_principals r ON r.principal_id = rm.role_principal_id
    ORDER BY r.name

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 22, 2011 9:50 PM
  • name name
    temp_common db_accessadmin
    temp_common db_backupoperator
    temp_common db_datareader
    temp_common db_datawriter
    NT AUTHORITY\Authenticated Users db_datawriter
    temp_common db_ddladmin
    dbo db_owner
    websensedba db_owner
    temp_common db_owner
    temp_common db_securityadmin
    Friday, July 22, 2011 10:00 PM
  • No wonder it works for temp_common, since this user has db_owner rights.

    But your original question was how add a member of "NT AUTHORITY\Authenticated Users" to db_datareader, and you were told to create a login for that member and add that login to db_datareader, and you told us that this did not work.

    However, I cannot see this login in this listing, so it seems that you have simply failed to add him to db_datareader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Monday, July 25, 2011 12:57 AM
    • Marked as answer by Peja Tao Wednesday, July 27, 2011 7:03 AM
    Saturday, July 23, 2011 8:39 AM