Common commands to manage a Sql Instance

  • Article

This post is a repository of commands that I have been commonly using to manage a Sql Instance.

List the sys admins on a instance

select [name] from sys.syslogins where sysadmin=1

Add yourself/someone as a sysadmin on sql instance

EXEC master..sp_addsrvrolemember @loginame = N'redmond\pranavra', @rolename = N'sysadmin'

EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'

EXEC master..sp_addsrvrolemember @loginame = N'REDMOND\aapfte', @rolename = N'sysadmin'

 

Remove yourself/someone as a sysadmin on sql instance

EXEC sp_droprolemember 'sysadmin', 'redmond\pranavra'

Running these commands if you do not have VS Pro and cannot connect to TSqlEditor

  • Open an admin prompt
  • Goto SQLInstall\Microsoft SQL Server\MSSQL.X\MSSQL\Binn eg D:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn
  • sqlcmd -SComputerName\InstanceName Eg Sqlcmd -S .\SQLEXPRESS
  • Execute the command as you need eg
    • select [name] from sys.syslogins where sysadmin=1
    • Go

Changing compatibility version of sqldatabase

EXEC sp_dbcmptlevel 'aspnet-WebApplication3-20120502172054', 90