create role just for read

Question

create role just for read

Tara-1044200

SSCoach

Points: 15951
More actions
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?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

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?

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

Tara-1044200

SSCoach

Points: 15951

August 27, 2010 at 6:08 pm

#1213345

can we create a group in sql server like windows?

pankushmehta Ten Centuries Points: 1217 More actions · Answer 4

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.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 5

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.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

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.

steve block SSCertifiable Points: 7698 More actions · Answer 7

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

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