Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-07-05 : 04:49:43
|
| I thought (SQL 2000 / Windows 2000 days!) that users who were members of (Windows) ADMINISTRATOR group could fire up SSMS and use Windows Authentication to connect as SA.Doesn't seem to be happening that way on our "2008 Web Edition of SQL" / "Windows 2008 R2 Enterprise Server" serversI think it is important that Administrator can use Windows Authentication to login as SA - we currently have had to set up DBAs with explicit SQL logins, and if we get into a pickle with their logins we won't be able to get back in(Starting SSMS with "Run as Administrator" doesn't fix the problem)I'd appreciate your thoughts on either a fix, or Best Practice if you lot no longer do it the "old way" |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-07-05 : 13:17:21
|
| Microsoft removed the BUILTIN\Administrators group from SQL Server 2008. You need to specifically add users to SQL Server 2008 and grant them sysadmin rights.Also, I disagree with allowing system administrators full access to SQL Server by default. This allows anybody that gains access to the server with local admin rights the ability to access the database with no ability to track who has accessed the system. I much prefer having to grant specific access using AD groups. This will make sure we know who is accessing the data. |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2010-07-09 : 09:17:34
|
| just an fyi, adding myself to each of the group worked.I could not upgrade the server as it would fail at the reporting services.Although I was a local admin, it would still not let me connect to the sql server at all.The days when DBAs were gods on SQL Server is going fast.RegardsParesh MotiwalaBoston, USA |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-09 : 09:38:11
|
quote:
Originally posted by pareshmotiwala
just an fyi, adding myself to each of the group worked.I could not upgrade the server as it would fail at the reporting services.Although I was a local admin, it would still not let me connect to the sql server at all.The days when DBAs were gods on SQL Server is going fast.RegardsParesh MotiwalaBoston, USA
No. As was stated above, being local admin on the server no longer makes you admin on the SQL instance (BUILTIN\Administrators is no longer automatically sa). |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-09 : 11:13:26
|
| Local windows admins can alway get into SQL Server in an emergency.There is a procedure documented in BOL, but basicically you just start SQL Server in single user mode, and any windows admin can connect as a SQL sysadmin.In SQL 2005, there is an even easier way to break in. Just add the login to the local group SERVERNAME\SQLServer2005MSSQLUser$SERVERNAME$MSSQLSERVER that is setup by the SQL Server installation. This group is what gives the service account sysadmin access to SQL Server, and anyone in the group becomes sysadmin.We have a domain Windows group setup that we put all our DBAs in, and we add that group as sysadmins on all SQL Servers as part of our installation process.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-09 : 17:44:49
|
| Thanks all for your help. I now know we weren't being thick (makes a change!) and have a suitable emergency solution. |
|
|
|
|
|
|
|
|
Administrators Windows Authentication