SQL Commands List-Database Servers

SQL Commands
Command	Description
ALTER DATABASE	Alters the selected database by adding or removing files.
ALTER TABLE	Alters a table within a database by altering, adding, or dropping columns.
ALTER VIEW	Alters a previously created view.
CREATE DATABASE	Creates a new database.
CREATE PROCEDURE	Creates a stored procedure.
CREATE SCHEMA	Creates a schema within a database.
CREATE TABLE	Creates a table within a database.
CREATE VIEW	Creates a database view to a table(s).
DELETE	Deletes rows from a table.
DROP DATABASE	Removes a database by deleting its files.
DROP PROCEDURE	Removes a stored procedure.
DROP TABLE	Removes a table from a database.
DROP VIEW	Removes a view from a database.
INSERT	Adds a new row to a table or view.
SELECT	Selects fields within a given table(s) for viewing.
USE	Uses a particular database for the commands following.

The best countermeasure for controlling stored procedures is simply to delete them. You can do so by taking these steps:

1.       Use the Microsoft SQL Server Enterprise Manager to open the database desired, usually Master.

2.       Expand the Extended Stored Procedures folder within the database.

3.       Right click on the stored procedure.

4.       For SQL Servers 7.x, 8.x, and 2000 select the Delete feature to remove the stored procedure.

If removing the stored procedures outright isn't an option before extensive testing is performed, you can restrict the permissions on the stored procedures. To perform ACLing on the SPs, follow this procedure:

1.       Use the Microsoft SQL Server Enterprise Manager to open the database desired, usually Master.

2.       Expand the Extended Stored Procedures folder within the database.

3.       Right click on the stored procedure.

4.       Select Properties.

5.       Click on the Permissions tab.

6.       Change the permissions as desired.

Part of the Transact-SQL reference, Microsoft SQL 2000 provides a litany of simple functions that can be called directly within an SQL string to provide valuable information. Here is a short list of helpful functions:

db_id()	Displays the database ID. Example: select db_id(); or use mydb; select db_id()
db_name()	Displays the currently used database name and is helpful in understanding what database is being used by default. Example: select db_name();
file_name(<ID>)	Displays the logical file name for the supplied file identifier (ID) and is helpful for enumerating all the files within a database. Example: select file_name(1) or use mydb; select file_name(1);
Getdate()	Displays the date and time on the system. select getdate()
object_name(<parm>)	Displays the database object name and is helpful in enumerating the various objects within a database. select object_name(1) or use mydb; select object_name(1)
setuser <user>	Impersonates a user but is valid only if existing user is member of sysadmin or db_owner fixed database role. setuser 'jane';
current_user	Displays the currently logged in user for the SQL session and is helpful in identifying the user making database queries. select current_user;

These Transact-SQL functions can be enormously helpful to an attacker.

An attacker would salivate over this information. Other keywords that can be used are:

CHECK_CONSTRAINTS

COLUMN_DOMAIN_USAGE

COLUMN_PRIVILEGES

COLUMNS

CONSTRAINT_COLUMN_USAGE

CONSTRAINT_TABLE_USAGE

DOMAIN_CONSTRAINTS

DOMAINS

KEY_COLUMN_USAGE

PARAMETERS

REFERENTIAL_CONSTRAINTS

ROUTINES

ROUTINE_COLUMNS

SCHEMATA

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TABLES

VIEW_COLUMN_USAGE

VIEW_TABLE_USAGE

VIEWS

Unfortunately, this functionality, also, is inherent in Microsoft SQL Server and cannot be shut off.

Master.dbo Default System Stored and Extended Stored Procedures
Procedure	Description
sp_addmessage	Adds a new error message to the sysmessages table.
sp_configure	Displays or changes global configuration settings such as c2 audit mode, allow updates, remote access, remote login timeout, user connections, and the like.
sp_help	Can enumerate just about anything on the SQL Server itself, including all objects.
sp_helpdb	Lists the databases available and, when used with a database name as a parameter, displays specific database information.
sp_helpprotect	Displays information on permissions for objects.
sp_OACreate	Creates an instance of the OLE object.
sp_OADestroy	Destroys an OLE object.
sp_OAGetErrorInfo	Displays OLE Automation error information.
sp_OAGetProperty	Displays a property value of an OLE object.
sp_OAMethod	Calls a method of an OLE object.
sp_OASetProperty	Sets a property of an OLE object.
sp_OAStop	Stops the OLE Automation stored procedure.
sp_password	Adds or changes a password for an SQL Server login. Examples: EXEC sp_password 'oldpass', 'newpass', 'sa'.
sp_tables	Displays the tables for the current database. Helpful in enumerating all the tables within a database. Examples: EXEC sp_tables or use mydb; EXEC sp_tables;.
sp_who	Displays information on SQL Server connections such as status, login name, host name where the connection is coming from, and database name and commands such as SELECT.
xp_availablemedia	Reveals the available drives on the machine.
xp_cmdshell	Runs arbitrary commands with administrator privilege.
xp_deletemail	Deletes a message from the Microsoft SQL Server inbox.
xp_dirtree	Allows a directory tree to be obtained.
xp_dsninfo	Displays the ODBC DSN information.
xp_enumdsn	Enumerates ODBC data sources on the server.
xp_enumgroups	Displays a list of Windows groups on the system. Example: EXEC master..xp_dirtree.
xp_eventlog	Displays event logs from the Windows system.
xp_fixeddrives	Displays the fixed drives on the system and its free space in MB.
xp_getfiledetails	Displays the properties of a given file. Example: EXEC master..xp_getfiledetails 'c:\winnt.ini'.
xp_getnetname	Displays the running system's NetBIOS name.
xp_grantlogin	Grants login rights of the specified user. Example: EXEC master..xp_grantlogin administrator.
xp_logevent	Logs a user-defined message in the SQL Server log file.
xp_loginconfig	Reveals information about the security mode of the server.
xp_logininfo	Displays the login information of the various users.
xp_makecab	Allows the user to create a compressed archive of files on the server (or any files the server can access).
xp_msver	Displays the Microsoft SQL Server version, including all information about the operating system. Example: EXEC master..xp_msver.
xp_ntsec_enumdomains	Enumerates domains that the server can access.
xp_readerrorlog	Displays the SQL Server error log.
xp_readmail	Reads a mail message in the SQL Server inbox.
xp_regaddmultistring	Adds a multi string registry key.
xp_regdeletekey	Deletes a registry key.
xp_regdeletevalue	Deletes a value within a registry key.
xp_regenumkeys	Enumerates registry key.
xp_regenumvalues	Enumerates registry key values.
xp_regread	Reads a registry key.
xp_regremovemultistring	Removes a multistrong registry key.
xp_regwrite	Writes to a registry key.
xp_revokelogin	Revokes access from a Windows group or user.
xp_sendmail	Sends a message to someone.
xp_servicecontrol	Allows a user to start or stop a Windows service. Examples: EXEC master..xp_servicecontrol 'start', 'schedule'.
xp_startmail	Starts an SQL Server mail client session.
xp_stopmail	Stops an SQL Server mail client session.
xp_subdirs	Displays a list of subdirectories.
xp_terminate_process	Terminates a process, given its process ID (PID).
xp_unc_to_drive	Unknown.