MSSQL Basic Cheat Sheet

Description

Example

Version SELECT @@version
List of Users SELECT name FROM master..syslogins
Current User SELECT user; — Returns user such as “dbo”SELECT user_name(); — Returns user such as “dbo”SELECT system_user; — Returns [DOMAIN]\[USERNAME]SELECT loginame FROM master.sysprocesses WHERE spid = @@SPID;
Privileges SELECT is_srvrolemember(‘sysadmin’);
SELECT is_srvrolemember(‘securityadmin’);
SELECT is_srvrolemember(‘serveradmin’);
SELECT is_srvrolemember(‘setupadmin’);
SELECT is_srvrolemember(‘diskadmin’);
SELECT is_srvrolemember(‘bulkadmin’);
SELECT is_srvrolemember(‘dbcreator’);———————————————————-SELECT name FROM master..syslogins WHERE sysadmin = 1;
SELECT name FROM master..syslogins WHERE securityadmin = 1;
SELECT name FROM master..syslogins WHERE serveradmin = 1;
SELECT name FROM master..syslogins WHERE serveradmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE setupadmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE diskadmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE bulkadmin = 1;
SELECT name FROM master..syslogins WHERE dbcreator = 1;
SELECT name FROM master..syslogins WHERE hasaccess = 1;
SELECT name FROM master..syslogins WHERE denylogin = 0;
SELECT name FROM master..syslogins WHERE isntname = 0;
SELECT name FROM master..syslogins WHERE isntgroup = 0;
List All Databases  SELECT name FROM master..sysdatabases;
Database by Id  SELECT DB_NAME(ID); — Where ID is 0, 1, 2, …, N
Current Database  SELECT DB_NAME();
List Columns SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name=’TABLE_NAME’); — Where TABLE_NAME is the table name on the current database
List Tables SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘U’; — Where DATABASE_NAME is the database which you wish to list the tables
List Views SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘V’; — Where DATABASE_NAME is the database which you wish to list the views
Hostname SELECT HOST_NAME();
Information and Location of DB file EXEC sp_helpdb DATABASE_NAME; — Where DATABASE_NAME is the name of the database
Share