July 18, 2008 at 8:15 am
First of all apologies if this is not the right place to put this topic as I'm a newbie.
We have two teams: network admins and DB admins. I'm a network administrator and when I remote to a SQL 2005 server (on the domain) I can use windows authenication to gain access to management studio. Our DB Admins do not have the same windows rights and when they remote to the server and try to log on to management studio it shows the message 'login failed for user, sql server error 18456'. There is also a message in the event log (windows server 2003) showing a failed audit. The DB Admins need to be able to administer all databases on the server. I'm not sure whether I should be adding the users to local groups or group policy. I would prefer not to make them local administrators. Any help would be much appreciated. 🙂
July 18, 2008 at 8:22 am
If I understand correctly DBAdmins is a windows group and it sounds like this group has not been granted access to the SQL Server. By default local administrators have sa rights on the SQL Server (something that should be changed by the DBA as soon as possible) so you have your rights because you are a domain admin. You need to go into SSMS -> Security ->Logins and add the DBAdmin windows group and assign it sysadmin rights. If you have one person who is the senior DBA then just give that Windows account the rights and let them do the rest. It does not sound like you have a true DBA on staff as they would have done the install and taken care of this already.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 18, 2008 at 8:38 am
Thanks for the quick reply. I'm having problems entering the login name, it asks for the name and password of an account with permissions to the database (me or the person I'm trying to add?) either way it shows access denied. Shouldn't I have full rights with my windows authentication?
July 18, 2008 at 8:47 am
Sorry, meant to mention that I tried adding the active directory group that I'd created with DB Admins in but the login appears to want a windows account and says it's not a valid Windows NT name.
July 18, 2008 at 8:54 am
You can add an activity directory group. You need to use the format domain\group (Domain\DB Admins) or use the Browse button and navigate to the group.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 18, 2008 at 8:59 am
The format you mention results in it not recognising the group name (which was copied and pasted). The search button shows username and password blank with no options in the drop-down and the '...' greyed out. (assumed frowning smiley)
July 18, 2008 at 9:01 am
Sorry, again I meant to ask, does this mean SQL 2005 is not integrated into active directory properly?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply