September 7, 2010 at 7:44 am
Hi All,
We've always used a mixed security level of windows authentication and sql authentication where I work. Currently, we're running SQL Server 2005 on Windows Server 2003, and we will be migrating to SQlServer 2008 R2 on Windows Server 2008 R2.
One of the interesting things (and a security hole) I noticed on SQL Server 2005, anybody in the administrator group can login to SQL Server 2005 and have access to all the data -- even though on SQL Server level, that authentication was never granted.
On SQL Server 2008, that access level **SEEMS** to have been shut off. i.e. if you have windows login, it does not automatically grant you access to the SQL Server like '05 did. Now we need to explicitly grant a windows login access to a database, as well as the server (and I'm cool with this).
Can somebody verify if this is the case?
In a nutshell it seemed on SQL Server 2005, the access level granted on a windows level took over whatever access was given on a SQL Auth level. But in SQL Server 2008, the only thing being authenticated is the password, BUT for data access levels, it is up to the DBA/whoever to grant access to the data.
Is this correct? Is there a simple grid out there that tells me the diff between 2005 and 2008 FROM A SECURITY LEVEL? Putzing around on Microsoft's site and google isn't giving me any easy answer (just pieces of it)
Thanks in advance!
September 7, 2010 at 8:22 am
In SQL 2005 and before, local admins (via the group builtin\administrators) were automatically granted sysadmin rights in SQL as part of the install process. You could then remove\reduce these rights after the install process if you wish.
In SQL2008 and 2008 R2 local admins get NO access to SQL via windows authentication by default as part of the install. there is an option to add a windows group with sysadmin rights in the install. This would normally be a group that contains members of DBA only.
So being a local admin on the server no longer gets you into SQL. won't stop you deleting\copying SQL files though.............
---------------------------------------------------------------------
September 7, 2010 at 8:36 am
...thanks. I see it now. Been thrown into a DBA role in the past year, and didn't know that.
Trial and error, live and learn i guess...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply