November 16, 2009 at 12:19 am
I logged into Windows machine using local administrator account. But when i tried to login to SQL Server 2008 , i get 'Login failed for the user <machine>\Administrator'.
How can i add the BUILTIN\Administrators group using oSQL commands to Security logins and also be given the 'sysadmin' role. I don't know sa password.
November 16, 2009 at 2:32 am
Check out BOL for sqlcmd or osql utility.
How ever you need to have a sysadmin privileges to add others to sysadmin role.
Edit:- Please be sure of the users you are adding to sysadmin role. You dont want to give this type of access to everybody.
"Keep Trying"
November 16, 2009 at 5:59 am
I used the following command after logging into windows machine as local administrator.
osql -SstrMachineName -UstrLogin -PstrPwd -i addsys.sql -ooutput.txt
(strLogin is an account which has login rights to SQL Server along with sysadmin role. I could use this account successfully to login using Management studio without any problem.)
addsys.sql has the following SQL commands:
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
GO
But i get the error in output.txt file : [SQL Server Native Client 10.0]Named Pipes Provider: Could not open a
connection to SQL Server [53].
[SQL Server Native Client 10.0]Login timeout expired
[SQL Server Native Client 10.0]A network-related or instance-specific
error has occurred while establishing a connection to SQL Server.
Someone pleeeeease help urgently !!!!
November 16, 2009 at 8:17 am
Chandu-212374 (11/16/2009)
I used the following command after logging into windows machine as local administrator.osql -SstrMachineName -UstrLogin -PstrPwd -i addsys.sql -ooutput.txt
(strLogin is an account which has login rights to SQL Server along with sysadmin role. I could use this account successfully to login using Management studio without any problem.)
addsys.sql has the following SQL commands:
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
GO
But i get the error in output.txt file : [SQL Server Native Client 10.0]Named Pipes Provider: Could not open a
connection to SQL Server [53].
[SQL Server Native Client 10.0]Login timeout expired
[SQL Server Native Client 10.0]A network-related or instance-specific
error has occurred while establishing a connection to SQL Server.
Someone pleeeeease help urgently !!!!
That error looks at first glance like SQL isn't running - or at least, not accepting connections. Can you open SQL Server Configuration Manager? Selet "SQL Server Services" on the left pane, and check to see if SQL Server is running. Then highlight SQL Server Network Configuration -> Protocols for [instance name], and look to see which are enabled/disabled.
(Edit: on a side note, consider using sqlcmd instead of osql - osql has been deprecated, and will be taken out of SQL Server at some point. Might as well get used to its replacement sooner than later. ;))
______
Twitter: @Control_Group
November 16, 2009 at 8:25 am
All are enabled. I could log in to Management studio using the same account without any problem.
November 16, 2009 at 10:01 am
Chandu-212374 (11/16/2009)
All are enabled. I could log in to Management studio using the same account without any problem.
From the same machine you were running osql on? If so, that's odd, certainly.
However, to solve the immediate problem - if you can log into Management Studio with that login, you should be able to run the SQL script in addsys.sql from there.
______
Twitter: @Control_Group
November 16, 2009 at 10:53 pm
I think the user is using osql to run the script from a diff machine. If so r u able to connect to your db server from the machine you are running osql. You could try to ping it. Also verify whthr the sql browser service is running on your server.
"Keep Trying"
November 17, 2009 at 12:05 am
Yes, but i have to write an automated script that would add BUILTIN\Administrators to the SQL Server Logins. And this script is run by Local administrator.
November 17, 2009 at 12:06 am
No, i am running the osql from same machine where SQL Server is installed.
November 17, 2009 at 6:29 am
It is normally bad practice to have BUILTIN\Administrators as a SQL login, and even worse for that login to have Sysadmin rights. Why do you need this - it typically allows a large number of people in your organisation who have very little skills in SQL Server to totally mess up your databases.
If this is for DBA access, then best practice is to have a Windows group dedicated to DBA team members and give this group logon and sysadmin rights.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
November 18, 2009 at 8:32 am
As stated before in this forum, you should not include all administrators, but if you need to add one more sysadmin and don't have a way to do it. Then this will help you.
http://www.sqlservercentral.com/articles/Administration/68271/
It is for SQL 2005 but I'm pretty sure it will work for 2008
November 23, 2009 at 3:23 am
Chandu-212374 (11/16/2009)
I logged into Windows machine using local administrator account. But when i tried to login to SQL Server 2008 , i get 'Login failed for the user <machine>\Administrator'.How can i add the BUILTIN\Administrators group using oSQL commands to Security logins and also be given the 'sysadmin' role. I don't know sa password.
- assuming non hacking scenarios: you can only add members to the sysadmin role when you use an account which is member of sysadmin.
- with the named pipe error: check your networking protocols on your serverstack and your clientstack (can be on same server). if one of 'm only has named pipes and the other only tcpip (for example) it wont work.
- solution to gain sysadmin when you dont have sysadmin access is:
(removed, missed post about sqlservercentral article for this)
- about
Yes, but i have to write an automated script that would add BUILTIN\Administrators to the SQL Server Logins. And this script is run by Local administrator.
this is the chicken or the egg problem. using common sense: it would be weird that an account with no sysadmin rights can assign itself sysadmin rights. :ermm:
if you (and your company) have no longer access to sql server i only know of the manual way on each server's console.
November 24, 2009 at 2:01 pm
Hi there
If you start SQL Server in single user mode, all users in local admin group will be able to log in as sysadmin even if BUILTIN\Administrators is not present.
It is by design to prevent data loss.
Stephen
HTH
June 27, 2012 at 3:30 am
Hi All ,
First Execute the below query. Then Grant apt permission thru Management Studio...
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
:w00t:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply