February 21, 2008 at 10:38 am
I'm a newby as far as SQL goes, but have been an MCSE for 11 years. I have a 2003 Active Directory and installed a SQL based program on one of my domain controllers. In order to install the program, it installed SQL Server Express on the DC and setup the DBs. From my workstation, I can connect to the SQL server using my network login, but if I login as a user that is not a member of the Domain Admins group, I cannot connect to the SQL server. So after a lot of trial and error, I finally figured out that if I add the other member to the Domain Admins group, I can connect to the machine. I cannot allow this user to be a member of the Domain Admins security group for ovious reasons. So I removed the user from the group then added them to the Builtin\Administrators group, and was able to connect to the SQL server. Still, I don't want this user to be a member of any Administrator group, so I removed it from this group as well. It seems that the only way that I can get this user to connect to the SQL server is by making them a member of one of these 2 groups.
How do I setup the security on the SQL server so that I can allow full access to the databases for this user without them being in either the Domain Admins, or the Builtin\Administrators group?
February 21, 2008 at 1:08 pm
Did you add the user's domain login to SQL Server? Builtin\Administrators is added by default during SQL Server installation. You'll have to add any other individuals or groups that you want to access SQL Server. See "Logins [SQL Server]" in BooksOnLine.
I don't use Express, but I think it comes with an version of SQL Server Management Studio which you can use to add logins. In Object Explorer, expand Security, right-click on logins, and select "New Login...". Once you've added the login, you can grant database access and other permissions.
Greg
February 21, 2008 at 1:19 pm
Yes I did. I figured it out shortly after posting the original message. I didn't have a chance to respond before you replied.
Thank you for your help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply