July 5, 2011 at 7:58 am
Greets,
The company I work for is in the process of moving all our databases from SQL 2005 to SQL 2008. I have a local account on the server box (let's call it SQLSVR2K8) and that account is a member of the group SQLSVR2K8\Administrators. I see that by default the group BUILTIN\Administrators is a member of the sysadmin fixed server role (as expected/as it has been in previous versions.)
But strangely I was unable to login using Windows auth via group membership. I called sys.xp_logininfo, and it correctly reflected a permissions path through BUILTIN\Administrators, but that diagnostic fact was inconsistent with behavior at login time.
Perhaps even stranger, I was able to connect to Integration Services, though it was rather useless, IS couldn't delegate my Windows login to the database engine any better than SSMS.
I had to explicitly add my user account to sysadmin, in order to connect to the database engine using Windows auth -- two brand new servers behaved the same way. (I was running SSMS directly on the server via terminal services.)
Has anyone seen the same?
TIA,
MM
[font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]
July 9, 2011 at 11:10 am
By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.
Regards,
WC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2011 at 12:28 pm
http://msdn.microsoft.com/en-us/library/cc280562%28v=SQL.100%29.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2011 at 6:51 pm
In my opinion it is good that if your an Admin on a box you no longer inherit the sysadmin role.
Every System Administrator had full control of SQL Server by default. :w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 11, 2011 at 2:46 am
Welsh Corgi (7/9/2011)
By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.Regards,
WC
In that case the IT dept guy that installed SQL 2008 explicitly added BUILTIN\Administrators (I saw that it was there, and assumed setup behavior was still consistent with the last 5 major releases.)
The hell of it is, even if BUILTIN\Administrators is added manually, group members are effectively not role members, even though xp_logininfo indicates otherwise. Worse, database engine authentication functionality is inconsistent with that of Integration Services.
So am I to understand that SQL treats BUILTIN\Administrators as a special case, and doesn't allow its group members to inherit SQL roles of which the group is a member? And the reason is they wanted to make sure there was absolutely no way that being a member of Administrators automatically makes and user a member of sysadmin?
That seems quirky, if a dba doesn't want Administrators members to be sysadmins, just drop the BUILTIN\Administrators group from the sysadmin role. Simple, straight-forward, effective...
That xp_logininfo's results fail to reflect the quirk is, imho, a flaw. It should not indicate that it has resolved a specific permissions path that, in the twisted new reality, isn't one. That the db engine and integration services work inconsistently is likewise a flaw -- what, this was important enough to do in SQL Sever but not Integration Services? Like it's ok if Administrators can implicitly login to IS, because SQL won't let them touch the database anyway?
[font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]
July 11, 2011 at 3:00 am
mmcginty (7/11/2011)
So am I to understand that SQL treats BUILTIN\Administrators as a special case, and doesn't allow its group members to inherit SQL roles of which the group is a member?
No, not at all. It's a standard group and behaves the same way all others do.
That seems quirky, if a dba doesn't want Administrators members to be sysadmins, just drop the BUILTIN\Administrators group from the sysadmin role. Simple, straight-forward, effective...
That's exactly what one does. That's why it's no longer a default in SQL 2008 and above
There is a potential problem when a person has access the the DB engine in more than one way. If they're a member of a local or domain group that has access and their account also has direct access with different permissions then there can be a resolution problem, where the person gets permissions of one or the other. Check that it is not the case here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 5:38 am
mmcginty
are builtin admins definitely in the sysadmin role, please execute the following against your instance and advise the results
EXEC sp_helpsrvrolemember 'sysadmin'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2011 at 6:17 am
No, not at all. It's a standard group and behaves the same way all others do.
I just tested it on my Windows 7 workstation, and NT group membership does work normaly, as you say. But on two different Windows Server 2008 systems it did not -- that fact being the specific reason for my original post.
Environmental factors that may or may not be relevant:
At least one of the servers is a domain member, my workstation is not.
The servers have a single instance of SQL installed, my workstation has 3 instances.
The Machine Debug Mgr (MDM) is on my workstation
One thing that's kind of interesting, when adding the Administrators group, if you try to add [machine name]\Administrators it fails for reason user/group name not found, you must use BUILTIN\Administrators. (I'm pretty sure that BUILTIN psuedo-domain is a SQL Server thing.) I'm not suggesting that this pertains to the behavior I encountered... but [machine name]\Administrators is a valid account name, how could it not be found?
-MM
[font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]
July 11, 2011 at 11:40 am
Perry Whittle (7/11/2011)
mmcgintyare builtin admins definitely in the sysadmin role, please execute the following against your instance and advise the results
EXEC sp_helpsrvrolemember 'sysadmin'
Yes definitely:
ServerRoleMemberNameMemberSID
sysadminBUILTIN\Administrators0x01020000000000052000000020020000
I noticed the SID for this account is the same on both the server and my workstation... which, logically would make any privileges/access granted to BUILTIN\Administrators valid, on any SQL Server, not good if you don't want anyone else to be able to attach a copy of the db file. But then again the SID for sa is always 1, an identical level of exposure...
-MM
[font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]
July 11, 2011 at 11:49 am
I did not mean to create a stir but if you have 20+ Network Admins & multiple service account with Admin Privileges and you are told to lock SQL Server down that is not a good position to be in.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 11, 2011 at 12:37 pm
mmcginty (7/11/2011)
which, logically would make any privileges/access granted to BUILTIN\Administrators valid, on any SQL Server, not good if you don't want anyone else to be able to attach a copy of the db file.
Frankly the only way you can prevent someone from attaching a DB file to another instance is with TDE. Otherwise it doesn't matter that SIDs are the same, they attach to a DB where they have a sysadmin account, they have complete access to the DB regardless of anything else.
The SID for windows authen accounts comes from AD/local machine. Since Administrators is a common group it'll have a common SID. Doesn't matter though, the SID doesn't really help anyone gain/block access anyway.
But then again the SID for sa is always 1, an identical level of exposure...
The fixed SID there gains you nothing at all. You can't login with a SID. Current recommendations for sa are renamed and/or disabled. I prefer disabled.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply