Post reply

Db_datareader role

  • March 26, 2012 at 6:54 am

    #258247

    How do I deny all the members of db_datareader role from viewing a column of a user table?

    If yes, can i achieve this with a script?

  • March 26, 2012 at 7:14 am

    #1464227

    You cannot deny or revoke permissions to special roles. Db_datareader happens to be a special role.

    You can make a workaround by creating a new database role <Role1> and adding this new role to the db_datareader role. Add users to <Role1> and

    DENY SELECT ON

    database.schema.table(column)

    TO <Role1>

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • March 26, 2012 at 10:10 am

    #1464393

    Thanks Calvo.

    Is it possible to rename a custom db role?

  • March 26, 2012 at 10:12 am

    #1464398

    Going out on a limb here and will say no.

  • March 26, 2012 at 10:35 am

    #1464408

    http://msdn.microsoft.com/en-us/library/ms189775%28v=sql.90%29.aspx

  • March 26, 2012 at 10:51 am

    #1464418

    Misread the question, read it that the OP wanted to rename db_datareader.

  • March 26, 2012 at 10:54 am

    #1464422

    Thanks Steve.

  • March 26, 2012 at 10:57 am

    #1464428

    Is it possible to move all the users(both windows and sql authnticated) from one database role to another using a script? In my case it would be from db_datareader to a newly created role.

  • March 26, 2012 at 11:12 am

    #1464442

    You cannot rename fixed roles (provided by SQL Server), as Lynn noted.

    For custom roles, if you want to move people from db_datareader, you can query the role to find members.

    EXEC sp_helprolemember 'db_datareader';

    Use a table of some sort to store these names, and then you can read through them, running sp_addrolemember to add them to the new role.

  • March 26, 2012 at 2:48 pm

    #1464562

    Thanks Steve, that answers all my questions.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply