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


BUILTIN\Administrators login doesn't appear to be working.


BUILTIN\Administrators login doesn't appear to be working.

Author
Message
pdonley
pdonley
Right there with Babe
Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)

Group: General Forum Members
Points: 791 Visits: 53
Hi All,

I am a newbie DBA working with SQL Server 2008 Express. I am having a strange problem that I have not encountered before on my other databases.

The way I understand it, if my particular user account is a member of the domain administrators group in my domain, and the domain administrators group is a member of the local Administrators group on the server I'm concerned about, and the SQL Server instance on that server has the login BUILTIN\Administrators granted sysadmin rights, shouldn't my user account then, by following the chain of permissions, be a sysadmin on that SQL Server instance?

The issue that I am experiencing is that this is not happenning on the instance I am working with. I had to have a co-worker explicitly add my domain user account as a login on SQL Server and grant it the sysadmin privelege in order to have those rights on the database.

If anyone has experienced something weird like this regarding security, I'd love to hear from you.

Thanks.
Lowell
Lowell
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166220 Visits: 41547
i believe in 2008 and above, they tightened the security rules on who gets admin of the sql server, and Builtin\Administrators was removed, so you have to be explicitly added in 2008 and above.

if you had an instance that was upgraded in place,, that group would still be there, but new installs, Builtin\Administrators needs to be added on purpose to give thoat group the rights you are expecting from the old days.

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!
pdonley
pdonley
Right there with Babe
Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)

Group: General Forum Members
Points: 791 Visits: 53
Lowell, I believe you are correct. However, I have already explicitly added "BUILTIN\Administrators" to my SQL Logins on SQL Server, and granted that login the sysadmin role. Yet, I still don't have the access I require.

Thanks.
Jayanth_Kurup
Jayanth_Kurup
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11849 Visits: 1351
BuiltIn\Administrators should not be added as SQL Login, add it as a windows login.
Check if the user who is trying to connect belong to the windows admin group.

Jayanth Kurup
pdonley
pdonley
Right there with Babe
Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)

Group: General Forum Members
Points: 791 Visits: 53
I apologize for being unclear. I am adding it as a Windows-Authenticated Login to SQL Server. They all appear together in the "Logins" folder under "Security". I used the wrong word there.

Thanks.
Lowell
Lowell
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166220 Visits: 41547
adding the login is not enough...did you add them to the server role as well;

something like this is what you are after, i think:

--Add/Restore Restore Builtin\Administrators
EXEC sp_grantlogin 'BUILTIN\Administrators'
EXEC sp_addsrvrolemember 'BUILTIN\Administrators','sysadmin'



after you've done that, make sure you are really part of the Builtin\Administrators group:

EXEC master..xp_logininfo
@acctname = 'Builtin\Administrators',
@option = 'members' -- show group members


does your windows login appear as part of that result set?

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!
bme
bme
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 38
Lowell (2/27/2012)
adding the login is not enough...did you add them to the server role as well;

something like this is what you are after, i think:

--Add/Restore Restore Builtin\Administrators
EXEC sp_grantlogin 'BUILTIN\Administrators'
EXEC sp_addsrvrolemember 'BUILTIN\Administrators','sysadmin'



after you've done that, make sure you are really part of the Builtin\Administrators group:

EXEC master..xp_logininfo
@acctname = 'Builtin\Administrators',
@option = 'members' -- show group members


does your windows login appear as part of that result set?


I have the same issue but even after adding BUILTIN\Administrators, I am not able to log on with any account in the Administrators group (except for the user Administrator that has a separate login configured). I have executed the mentioned xp_logininfo SP and it lists the users in the Administrators group but they don't work as logins for the Database Engine. I can use them to login for other parts of the sql server, though. I did check that 'BUILTIN\Administrators' is a member of the sysadmin role.

I am at a loss right now.
bme
bme
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 38
I found the reason for my trouble. I had to right-click SSMS and run it as Administrator. UAC is really a bitch.
Paul Nevlud
Paul Nevlud
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 38
Thanks BME. The same was happening for me. In my case, I had disabled UAC on a Win2008 machine, but then cloned the machine and renamed it. Now, the UAC seems to be quasi re-enabled, even though it is still set to off.



nitefalll
nitefalll
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 18
First let me say I'm a huge fan of Lowell.

We attempted to add a new administrator to a 2012 box like we've done before with 2008R2 and previous. That's what brought me to this post. My "server" is running under a desk and we just hammered it together quick. We run SSIS packages from it for now and only until our new budget when we can buy a real server. It also happens to be my desktop.

Saw this post and ran the sql. I can now see the admins but no one can log in. We can ping from a dos prompt but we can't connect via SSMS. The connection just times out. Digging further we noticed there was 2 SqlServers running on this box. A 2008R2 scratch database and the 2012. Jumped into the configuration manager, I realized what we did. The 2008R2 had the TCP/IP and Named pipes enabled protocols. The 2012 it was not. Yes, this is not good practice but kind of day-to-day real life. Turned off the connections for the 2008 server then enabled the connections for the 2012 server processes and restarted the services.

Now we can see the server by it's machine name. Then we tried to log into SSIS, and failed for a odd error. Seems the Dcom has been tightened down too. Went here:

https://msdn.microsoft.com/en-us/library/hh213130(v=sql.110).aspx

That actually worked in a dos prompt. Dcomcnfg.exe fired up on our win7 platform and we made the recommended changes.

Just thought I'd add the newbie bit to check the protocol connections because locally it worked fine but network wise nothing. Thanks again everyone for posting this. It helped a lot.
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