grant users with read access to 20 databases
-
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
-
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!
-
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.
-
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.
-
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! -
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....
-
Lowell, just curious how did you add the image to the post?
-
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)
-
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! -
Thanks for both of you.
I see your point of using it.
Only at the time which is really helped and needed to visualize
-
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.
-
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! -
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply