BUILTIN\Administrators login doesn't appear to be working.
-
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.
-
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! -
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.
-
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.
-
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! -
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.
-
I found the reason for my trouble. I had to right-click SSMS and run it as Administrator. UAC is really a bitch.
-
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.
-
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.
-
nitefalll (3/25/2015)
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.
Shifting gears a bit and speaking from experience way back when I was in PC support a million or so years ago... its a really bad idea to have any computer within two feet of the floor. There's a dust layer at about that height and computers below the 2ft level (especially if you have carpet on the floor) will need to be cleaned internally (or filters changed if you have them) a whole lot more frequently.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
sounds like windows firewall to me.
you can prove it real quick by simply turning off windows firewall, and trying to connect from another machine.
the right thing to do after that, is to add a specific exception for port 1433(assuming you are using the default port/only instance?)
1.Go to the control panel and open windows firewall.
2.click on the Advanced Setttings Link; you might get prompted with the UAC security prompt.
3.Make sure the Inbound rules item is selected,a nd way over on the right, click new rule.
4.Choose a rule type of Port and click next.
5.Put 1433 in the port box. make sure TCP is selected.
for reference, bcause i have four instances on my machine, i have four ports open:
1433,12008,12012,12014 which is for my default instance, a SQL 2008R2, a SQL2012 and a SQL2014; i just mad esure those isntnaces listen to a static port instead of dynamic.
6. The rest is all down hill, with nothing to really note; press next...Next .next until you are prompted to give it a name.
7. At the Namingof Rule Prompt,give it a meaningful description like "SQL Server External Access" or something and hit save.
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! -
Jeff Moden (3/25/2015)
Shifting gears a bit and speaking from experience way back when I was in PC support a million or so years ago... its a really bad idea to have any computer within two feet of the floor. There's a dust layer at about that height and computers below the 2ft level (especially if you have carpet on the floor) will need to be cleaned internally (or filters changed if you have them) a whole lot more frequently.I didn't know that, Jeff, though I have experienced it. I once had to service a Wang Archiver workstation (mid/late '80s) that had failed fans, the thing was so hot that you couldn't touch it until it had cooled for half an hour. I took the case apart and could turn it upside down and shake out the cruft, apparently the fans had failed a long time ago and they didn't worry about it until it started smoking.
I'll bet it's especially bad here as we're next to White Sands National Park.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply