grant users with read access to 20 databases

Question

Post reply

grant users with read access to 20 databases

sqlfriend

SSC Guru

Points: 52781
More actions
August 1, 2011 at 5:00 pm

#254788

We have about 20 archived database on a server along with other databases.
We would like to grant about 10 people read access to those 20 archived databases.
What is the quick and best way to do it?
I'm thinking to create a role, and grant the role access to those databases, and then add those 10 users to the role.
But I don't know how to create a role on server level instead of on each database for this purpose,
or any other options?
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jinu43 Ten Centuries Points: 1237 More actions · Answer 1

SQL Server has "db_datareader" role for read access to the database level.

Below is t-sql command and you can also perform this task using GUI.

use mydb

go

exec sp_addrolemember db_datareader, MYUSER

go

Thanks!

chandan_jha18 SSChampion Points: 11330 More actions · Answer 2

sqlfriends (8/1/2011)
We have about 20 archived database on a server along with other databases.
We would like to grant about 10 people read access to those 20 archived databases.
What is the quick and best way to do it?
I'm thinking to create a role, and grant the role access to those databases, and then add those 10 users to the role.
But I don't know how to create a role on server level instead of on each database for this purpose,
or any other options?
Thanks

Ask your windows guys to create a n AD group and then map all these domain users to that group. Once it is done, add the add group to the db_reader role across all the servers.

I find it the easiest way to do that.

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 3

add the add group to the db_reader role across all the servers.
I find it the easiest way to do that.

Hi Can you please let us know how to add windows group to authenticate all user in that group on SQL Server. I think it is not possible.

Lowell SSC Guru Points: 323518 More actions · Answer 4

ashok.faridabad1984 (8/2/2011)
add the add group to the db_reader role across all the servers.
I find it the easiest way to do that.
Hi Can you please let us know how to add windows group to authenticate all user in that group on SQL Server. I think it is not possible.

As stated, get your network admin to create a group witht eh twenty specific users in it.

after that is created, create a login for the group in SSMS:

that step, if you choose to script it instead, produces a script like this:

USE [master]

GO

CREATE LOGIN [MYDOMAIN\BusinessGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

now that a login for the group exists, you go to each of the "readonly" databases and run this script to create users, a role, and the permissions.:

USE [SandBox] --my db for users to do stuff.

GO

CREATE ROLE [ReallyReadOnly]

--give my new role READ permission to ALL tables

EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

--now add the USER for the LOGIN

GO

CREATE USER [MYDOMAIN\BusinessGroup] FOR LOGIN [MYDOMAIN\BusinessGroup]

GO

--add my windows role to the role containing the "right" permissions.

EXEC sp_addrolemember N'ReallyReadOnly', N'MYDOMAIN\BusinessGroup'

repeat that tiny snippet in each database they should have access to.

to test their permissions, you don't need to login as them , you can impersonate them isntead.

Choose a user you KNOW is in the new group:

--chnage into someone else

execute as user='MYDOMAIN\lowell'

BEGIN TRAN

select * from sometable --do i have read permissions?

delete from sometable where 1=2 -- do i have delete permissions

ROLLBACK TRAN

revert; --change back into myself

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!

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 5

Great ! your response is quite satisfactory. Thank You very much. My confusion was I created a user group on the local system and then added domain users in that group and then authenticate that group on the server andwhen i tried to login into the server once of user added in local user group I could not thats why I thought it is not possible, I anyway really appreciable for fading away my misconception or confusion....

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Lowell, just curious how did you add the image to the post?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

sqlfriends (8/2/2011)
Lowell, just curious how did you add the image to the post?

Upload the image, then right click on the attachement and hit copy link.

Then use the img tags and paste that link.

P.S. don't cross post, you wasted both Lowell's and my time on this rather than just 1 of us (don't worry I'm not mad, by Lowell might be :-P)

Lowell SSC Guru Points: 323518 More actions · Answer 8

Ninja's_RGR'us (8/2/2011)
sqlfriends (8/2/2011)
Lowell, just curious how did you add the image to the post?
Upload the image, then right click on the attachement and hit copy link.
Then use the img tags and paste that link.
P.S. don't cross post, you wasted both Lowell's and my time on this rather than just 1 of us (don't worry I'm not mad, by Lowell might be :-P)

:crazy: Grrr...

Like my friend Remi said, it's just the img tag; Any image i might reuse i put on my own server, since it doesn't cost me anything.

I try to post images and code examples when it seems appropriate, as i think it helps to visuallize better.

now if i could only afford a spell checker.....

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!

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

Thanks for both of you.

I see your point of using it.

Only at the time which is really helped and needed to visualize

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Lowell (8/2/2011)
Ninja's_RGR'us (8/2/2011)
sqlfriends (8/2/2011)
Lowell, just curious how did you add the image to the post?
Upload the image, then right click on the attachement and hit copy link.
Then use the img tags and paste that link.
P.S. don't cross post, you wasted both Lowell's and my time on this rather than just 1 of us (don't worry I'm not mad, by Lowell might be :-P)
:crazy: Grrr...
Like my friend Remi said, it's just the img tag; Any image i might reuse i put on my own server, since it doesn't cost me anything.
I try to post images and code examples when it seems appropriate, as i think it helps to visuallize better.
now if i could only afford a spell checker.....

Ok, Lowell I'll let you explain the image trick and I'll go right ahead and edit my post out :-P.

Lowell SSC Guru Points: 323518 More actions · Answer 11

lemme see where i put Remi's password again....

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!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Lowell (8/2/2011)
lemme see where i put Remi's password again....

No need to look, here it is (yes it's actually a real password)

pX725JwUGbe4e7SO2Q