2

Working with a new SQL Server 2016 Express installation. I loaded SSMS and have Administrator access to the instance. The vendor supplied a database for a new app that has only a couple internal accounts for their app defined in the database's Security. I only have public access to the database at this point- I can look at properties, but cannot add accounts to the database.

Is there a way to add my administrator account to this database's Security to gain admin access? SQL downtime is not an issue. I did some searching and found a handful of hits for regaining access to the instance, but haven't found any that address adding a new account at the database level.

1
  • 1
    When you right click your login at the instance level > properties > Server Roles, what server roles are marked?
    – Ronaldo
    May 1, 2020 at 12:17

2 Answers 2

2

If you are a Windows Administrator, follow the procedure documented here.

Or simply run this script from an elevated command prompt on the SQL Server:

net stop mssql$sqlexpress 
net start mssql$sqlexpress /mSQLCMD 
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

Source

0

I did finally get admin access to the SQL database using the PsExec utility to run SSMS as an Administrator. Made a mistake the first time I tried this- the missing piece was that I needed to open the Command Window as an Administrator (right-click, run as Administrator). Replaced the path below with the one where the SSMS 18.5 executable lives. Right-click the SSMS shortcut and view properties to find that.

Once SSMS launched, I found I could add any account I wanted. But... I don't have a completely happy ending: I found that the vendor's new software release is encrypting some of the critical text columns in the tables. Unless I can figure that out (ha, right), I am unable to use the reporting scripts I had previously written for the prior release. The app's built-in reporting is on the high side of hideous.

Thanks to everybody for the responses. I hope this can be of help to others.

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.