Sql-server – SQL Server 2016 Express, need to add account with Administrator access to a database

Securitysql serversql-server-2016

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.

Best Answer

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

Related Question