SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL2008 Unable to Add Built-In\Administrators


SQL2008 Unable to Add Built-In\Administrators

Author
Message
Stamey
Stamey
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 1051
I installed a new SQL2008 instance, the only one, on a VM Win2008 server for development. For reasons beyond my knowledge or control, they set this server up on a segment behind a firewall and put a copy of a DC from the production domain in there with it.
The Win2008 server was part of the domain "copy" before the SQL2008 installation began. When I installed SQL2008 I put the user I was logged in as, and the local admins group into the sysadmins group, as part of the setup process.
Once the install was complete, I tested and successfully logged in to the SQL Server using SSMS and the login I used to install SQL Server.
A new login was created by someone else and made a part of the Domain Admins group for the domain this server resides in. This new login is unable to connect to SQL Server when logged into the local machine and using SSMS. I have been through and found that the account is in the domain admins group, and the domains admins group is in the local administrators group on the SQL Server, and the local administrators group was set up as a SYSAdmin role in SQL 2008. After this verification I removed the local Administrators group from SQL Server and attempted to add it back in. SQL Server was unable to find it, giving me the error:
Windows NT user or group 'Server4\Administrators' not found. Check the name again. (Microsoft SQL Server, Error: 15401)

I tried rebooting the box to see if that would help, but no luck. I successfully added the "Domain Admins" group to the SQL 2008 server as Sysadmins and the user account in question can now connect.
What I am trying to figure out is why the local Administrators group cannot be added to SQL 2008. I know that it is not added by default on installation as an added security feature (finally), but I should be able to add it in later if I want to.

Anyone else run into this behavior?

Thanks,
Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28760 Visits: 39980
i have this snippet saved from SQL 2000, which is supposed to add back Builtin\Administrators if it was removed;

i ran it on my SQL2008 without errors, but that doesn't technically prove anything.
does this help at all?

exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
USE master
go
EXEC sp_grantlogin 'BUILTIN\Administrators'
go
EXEC sp_defaultdb 'BUILTIN\Administrators', 'master'
go
EXEC sp_defaultlanguage 'BUILTIN\Administrators', 'us_english'
go
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'
go
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname='BUILTIN\Administrators')
PRINT '<<< CREATED LOGIN BUILTIN\Administrators >>>'
ELSE
PRINT '<<< FAILED CREATING LOGIN BUILTIN\Administrators >>>'
go



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Stamey
Stamey
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 1051
I am trying to get another server for working out this problem set up now. The original server where this problem exists is needed for an important project so I need to lay off that box right now. I a should be able to get a duplicate of that box, as it is a VM, less the SID, name and IP address, that I can test with.

Thanks,
Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5942 Visits: 3866
Security has changed in Windows 2008 and above. The problem you are seeing is not caused by SQL Server, but be Windows 2008.

You used to be able to add a domain group to a local group, and have the domain group members inherit the rights of the local group. This has changed with W2008. If you look at the W2008 documentation you will see that many of the local groups are now shown as deprecated, and already the security works in a different way for these groups.

From what I can see, any login that has special rights by way of their local group membership now has two security contexts: a normal User context and a privileged context. Everything that the login does is run under the normal user context unless they specifically request the privileged context (i.e. Run as Administrator).

One impact of this is that if you add Domain Admins to the local admin group, when they connect to SQL Server their security context is no longer 'Local Administrators'.

One way I have found to overcome this issue is to add the relevant groups directly into SQL Server, and not rely on inherited permissions from the local server groups.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Stamey
Stamey
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 1051
Ed, I do believe you are on to something, but I experienced the same problem with adding "Domain Admins" to the server with the SysAdmin role, the next day. I mean that my original band-aid stopped working the next day. The new band-aid I put on it, until I can fully investigate and understand the problem/resolution, was to create a new local group on Win2008 called "SQLAdmins", add the "Domain Admins" to it then add that local group to the SQL Server with SysAdmin role.
I am hoping I will get the copy of the server soon so I can mess with it a bit and try different thing to fully understand what's happening.
Some of my other research on the Internet has suggested it's UAC (user access control) causing the problem, and this would be closely related to what you describe.

Thanks,
Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search