October 12, 2017 at 1:57 pm
We are trying to gain admin access to an SQL2008R2 Express instance. Unfortunately it's sort of a rogue install, someone let the vendor rep set it up and the account used by the vendor rep has long gone as part of an inactivity purge. We've tried the various techniques on the internet without success. I'm wondering if there is something I am missing. (Yes the data will be moved to a real instance with admin access)
The instance is set up as windows authentication only. Attempting to use
OSQL sp_password NULL, <pwd>, 'sa'
results in 'does not exist or you do not have permission'
Trying to add my AD account as a sysadmin role member does not work either
sp_addsrvrolemember '<AD ACCOUNT>', 'sysadmin'
'User does not have permission to perform this action.'
I tried the process described at
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out
But this failed as well. The ALTER SERVER ROLE failed for syntax because it's 2008, but I tried:
sp_addrolemember 'sysadmin', '<AD account>'
failed with 'the role sysadmin does not exist in the current database'
...
-- FORTRAN manual for Xerox Computers --
October 12, 2017 at 2:22 pm
Instead of running "sp_addrolemember", try running
sp_addsrvrolemember <AD account>,'sysadmin'
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 16, 2017 at 11:51 am
jay-h - Thursday, October 12, 2017 1:57 PMWe are trying to gain admin access to an SQL2008R2 Express instance. Unfortunately it's sort of a rogue install, someone let the vendor rep set it up and the account used by the vendor rep has long gone as part of an inactivity purge. We've tried the various techniques on the internet without success. I'm wondering if there is something I am missing. (Yes the data will be moved to a real instance with admin access)The instance is set up as windows authentication only. Attempting to use
OSQL sp_password NULL, <pwd>, 'sa'
results in 'does not exist or you do not have permission'Trying to add my AD account as a sysadmin role member does not work either
sp_addsrvrolemember '<AD ACCOUNT>', 'sysadmin'
'User does not have permission to perform this action.'I tried the process described at
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-outBut this failed as well. The ALTER SERVER ROLE failed for syntax because it's 2008, but I tried:
sp_addrolemember 'sysadmin', '<AD account>'
failed with 'the role sysadmin does not exist in the current database'
It almost looks like you have done a combination of techniques but maybe exactly the one from the documentation.
When you first did this:
sp_addsrvrolemember '<AD ACCOUNT>', 'sysadmin'
And got the error:
'User does not have permission to perform this action.'
That could be from not being a member of the local admins group. You would also need to do a right click on SSMS and select run as administrator. One of those two was likely missing from the process. You can also use sqlcmd - don't use osql anymore since sqlcmd has replaced it.
After you start SQL Server in single user mode, you need start the command prompt using Run As Administrator. Or SSMS using Run As Administrator. If you get all pieces in place, you can use
sp_addsrvrolemember '<Domain\YourAccount>', 'sysadmin'
Sue
October 16, 2017 at 12:13 pm
Thanks
...
-- FORTRAN manual for Xerox Computers --
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply