Post reply

create role just for read

  • August 26, 2010 at 9:50 am

    #387669

    can i create a sql role and add some users(50+) and give them only read access to all the databases in the server. What would be the best way to achieve this?

  • August 26, 2010 at 10:36 am

    #1212416

    Tara-1044200 (8/26/2010)

    can i create a sql role and add some users(50+) and give them only read access to all the databases in the server. What would be the best way to achieve this?

    I don't think so; roles exist on a per-database, and you cannot add new server roles, only use the built in ones right? so if you had 10 databases, you could add a role [ReallyReadOnly] to each of the databases, and then add the users that role in each of the databases; but if you add Database 11, you would have to add the role to that seperately,a nd then grant it again; logins need a matching user to get into each database UNLESS they are sysadmins, right?

    you could automates that creation and stuff of that role and the users to belong to it, but i don't think what you are asking is built in.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 26, 2010 at 10:47 am

    #1212424

    Actually i am looking for a simple method to give read access to a group of users by default on a server to all databases. I know if i can create a windows group and give read access to the on all databases and keep adding users to that group will work but how could i do the same without windows group?

  • August 27, 2010 at 6:08 pm

    #1213345

    can we create a group in sql server like windows?

  • August 29, 2010 at 8:49 pm

    #1213590

    Tara-1044200 (8/27/2010)

    can we create a group in sql server like windows?

    You can create a windows group (or AD group) and grant it read permission to all the databases. All you need to do after that is add the users to that group.

    you can use "sp_msforeachdb" and create a nice easy script to grant read access to each database.

  • August 30, 2010 at 8:49 am

    #1213776

    Tara-1044200 (8/27/2010)

    can we create a group in sql server like windows?

    No, as Lowell previously mentioned, you cannot add new Server roles - and I think that is basically what you asking to do. You can only add new roles (groups) at the database level.

    So I think you have 2 options

    (a) For Win logins, use Active Directory to establish the group and grant it db_datareader in each database.

    (b) For SQL logins, script the grant to each sql user for db_datareader and loop through each database and execute it.

  • August 30, 2010 at 9:39 am

    #1213803

    I wouldn't add db_datareader. I find exceptions constantly for tables, like auditing tables. I would create a role for each db, add the Windows group to it, assign rights to all the tables that you need. It might be all tables, but it might not be.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 31, 2010 at 8:17 am

    #1214333

    I you really want to be quick and dirty, I believe you could add the public role as a member of the db_datareader role in each database. I've never tried it and don't recomend it but I would think it would work.

    As Steve pointed out, it seems like there are always exceptions that come up if you use the db_datareader role and then you have to go back and redo all the setups.

    Steve Block

  • August 31, 2010 at 8:37 am

    #1214357

    I think public has the same issues as datareader. It doesn't handle exceptions well.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

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

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