March 10, 2008 at 3:56 pm
Hi,
I have the following scenario:
Setup: SQL 2005 SP2
Both SQL DB engine and SQL Agent services run under Local System Account
I have a windows login which is a member of the windows local admin group. This login is used to run an application which is also on the same machine as SQL 2005. The application uses the local SQL 2005 as its database and connects via this specific windows login to execute specific stored procs.
Now, for security purposes, I would like to remove the Builtin\Administrators group from SQL 2005 (so that windows admins won't be able to go into the database as sysdamins). I do all my sysadmin work using "sa". The problem is that when I removed access to the Builtin\Administrators group, the windows login used by the local application cannot connect to SQL 2005 anymore! This is by virtue of the specific login being a member of the windows local admin group. I asked the app admin and he said that this specific login needs to have windows local admin rights in order to function properly.
How do I go about giving database access to this specific windows login to SQL 2005 (i.e. while maintaining his windows local admin membership and Builtin\Administrators group still completely removed from SQL 2005)? On a related note, if I removed Builtin\Administrators from SQL 2005, can I still run the SQL services under Local System account?
Thanks for any advice!
March 10, 2008 at 5:20 pm
Just add the app login in as a specific Widnows login. Then authorize it in SQL server for what it needs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 5:28 pm
Thanks for your reply.
When you mean "add as a specific Windows login", do you mean just use another windows login as the app login to SQL 2005?
In this regard, I don't think I have a choice to change the app login used. I am forced to use this login and, unfortunately, it has to maintain its membership in the Windows local admin group.
March 10, 2008 at 6:16 pm
No, just add that Windows Account as a Login on your SQL server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 6:23 pm
Yes, I think I've got it now...:)
Even if the "Builtin\Administrators" group is completely deleted from SQL 2005, I can still add this specific Windows login to SQL 2005 (even if it has local admin membership) --> I tried it and it seems to work.
With regard to using the Local System Account to run the SQL services, will there be any adverse effects if I remove the Builtin\Adminstrators group from SQL 2005 (with the idea being System accounts are also members of the Windows Administrators group by default)? By the way, "NT AUTHORITY\SYSTEM" has sysadmin access in the SQL 2005 database (installed by default).
Thanks for your help.
March 10, 2008 at 6:44 pm
Glad I could help.
(sorry, don't know about your other question)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply