April 3, 2011 at 3:55 pm
I have a group in AD that logins to windows and what i want to do is, the same AD group to access also SQL Server using the AD credentials. SQL Server is setup to use mixed mode (windows and sql server login).
So lets say my AD group name is : fxb-dss-fsx-sqladmin, this group(2 user in that group) have access to win 2008(OS) only. Same group have to access SSMS also.
OS: Win 2008
SQL: 2008RC
Total sql server like 50 and SQL Instance is 100, so that group can access to all server and instance's.
so i want to give access to the above group to use also SQL Server.
can anybody guide me how to do this?
Thanks.
New DB :w00t:
April 4, 2011 at 3:14 am
Add the group to sql server logins on all of your servers.
You're not saying much about the role of the users in this group, can you please specify what will these users be doing with SQL Server?
Administration? Ad-hoc querying? Run applications?
-- Gianluca Sartori
April 4, 2011 at 4:03 am
Hi,
Thnx for asking.
AD group name is : fxb-dss-fsx-sqladmin, this group have(2 user) access to win 2008(OS).
This group is SYSADMIN.
I have around 50 Sql Server and more then 100 Instance this group can able to access all server & Instance's, NO 3rd party tools we used here.
April 4, 2011 at 4:18 am
You will have to issue this statement on each of the target servers:
CREATE LOGIN [YOURDOMAIN\fxb-dss-fsx-sqladmin] FROM WINDOWS
WITH DEFAULT_DATABASE= [tempdb],
DEFAULT_LANGUAGE= [us_english]
GO
EXEC master..sp_addsrvrolemember @loginame = N'YOURDOMAIN\fxb-dss-fsx-sqladmin',
@rolename = N'sysadmin'
GO
One option could be running this script from a multi-server connection window in SSMS. Go to registered servers, add a new group (e.g. "My Servers") and add server registrations for all your servers. Then right-click the server group and select "New Query". The batch will be run against all the servers in the server group.
Another option could be adding the users to Domain Administrators, so that it pops up to sysadmin role through "BUILTIN\Administrators". I don't recommend this, it makes things a bit unclear and overlaps SQL Server sysadmins with Windows admins.
Another option could be running this script with PowerShell or a batch file, but it would require some simple coding.
Hope this helps
Gianluca
-- Gianluca Sartori
April 4, 2011 at 5:53 am
Thank you Gianluca !
i got this error
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'xyd domain name\fxb-dss-fsx-sqladmin'' not found. Check the name again.
Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68
What is easy way to deal with problem, due to i am new-db. Details information to solve this issue, please.
Thanks again.
New DB
April 4, 2011 at 6:17 am
M.Kahn (4/3/2011)
I have a group in AD that logins to windows and what i want to do is, the same AD group to access also SQL Server using the AD credentials. SQL Server is setup to use mixed mode (windows and sql server login).So lets say my AD group name is : fxb-dss-fsx-sqladmin, this group(2 user in that group) have access to win 2008(OS) only. Same group have to access SSMS also.
OS: Win 2008
SQL: 2008RC
Total sql server like 50 and SQL Instance is 100, so that group can access to all server and instance's.
so i want to give access to the above group to use also SQL Server.
can anybody guide me how to do this?
Thanks.
New DB :w00t:
I already answered this question 2 days ago
http://www.sqlservercentral.com/Forums/Topic1087470-1550-1.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2011 at 6:31 am
Here's the actual working link...
http://www.sqlservercentral.com/Forums/Topic1087470-1550-1.aspx
April 4, 2011 at 8:08 am
my apologies i have fixed the link now
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2011 at 8:13 am
Perry Whittle (4/4/2011)
my apologies i have fixed the link now
Still not working!
April 4, 2011 at 8:21 am
should be now
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2011 at 8:27 am
Perry Whittle (4/4/2011)
should be now
Yup.
I was starting to think this was a late april's fool joke! :hehe::Whistling:
April 4, 2011 at 8:33 am
no, i put "" around the url which is ok for Safari but IE doesnt like it :crazy:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2011 at 8:39 am
Ya but since IE still somewhats rules the market :w00t:...
April 4, 2011 at 8:41 am
Perry Whittle (4/4/2011)
no, i put "" around the url which is ok for Safari but IE doesnt like it :crazy:
Neither does Chrome FYI
-- Gianluca Sartori
April 6, 2011 at 7:10 am
Hi,
Thanks to Gianluca Sartori, Perry Whittle & all who answer.
It's help me a alot and from my bottom of heart, thank you(Gianluca Sartori & Perry Whittle) dude.
Keep do and keep go!
Kahn
🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply