3 out of 4 rated this helpful - Rate this topic

System Stored Procedures (Windows Azure SQL Database)

Some administrative and informational activities can be performed by using system stored procedures. Microsoft Windows Azure SQL Database does not support all the system stored procedures provided by SQL Server. This topic describes the support for the system stored procedures.

The following table lists the system stored procedure categories and also describes whether or not Windows Azure SQL Database provides support for each category:

System Stored Procedure Category	Windows Azure SQL Database Support
SQL Database Stored Procedures	Yes
Active Directory Stored Procedures	No
Catalog Stored Procedures	Partial
Change Data Capture Stored Procedures	No
Cursor Stored Procedures	No
Database Engine Stored Procedures	Partial
Database Mail and SQL Mail Stored Procedures	No
Database Maintenance Plan Stored Procedures	No
Distributed Queries Stored Procedures	No
Full-Text Search Stored Procedures	No
Log Shipping Stored Procedures	No
Automation Stored Procedures	No
Policy-Based Management Stored Procedures	No
Replication Stored Procedures	No
Security Stored Procedures	Partial
SQL Server Profiler Stored Procedures	No
SQL Server Agent Stored Procedures	No
XML Stored Procedures	No
General Extended Stored Procedures	No

Important
For more information about SQL grammar and stored procedure usage and examples, see System Stored Procedures (Transact-SQL) in SQL Server Books Online.

SQL Database Stored Procedures

Several stored procedures are specific to Windows Azure SQL Database and are not supported in on-premise SQL Server. These stored procedures are listed in the following table:

sp_set_firewall_rule (Windows Azure SQL Database)	sp_set_database_firewall_rule (Windows Azure SQL Database)
sp_delete_firewall_rule (Windows Azure SQL Database)	sp_delete_database_firewall_rule (Windows Azure SQL Database)

Catalog Stored Procedures

Windows Azure SQL Database supports the catalog system stored procedures in the following table as is. These stored procedures implement ODBC data dictionary functions and isolate ODBC applications from changes to underlying system tables.

`sp_column_privileges`	`sp_sproc_columns`
`sp_columns`	`sp_statistics`
`sp_fkeys`	`sp_stored_procedures`
`sp_pkeys`	`sp_table_privileges`
`sp_special_columns`	`sp_tables`

Windows Azure SQL Database does not support the sp_server_info and sp_databases catalog system stored procedures.

Database Engine Stored Procedures

Database engine stored procedures are used for general maintenance of an instance of SQL Server. The following table lists the database engine stored procedures that Windows Azure SQL Database supports:

`sp_autostats`	`sp_helpconstraint`	sp_refreshsqlmodule (Windows Azure SQL Database)
`sp_createstats`	`sp_helpindex`	`sp_refreshview`
`sp_datatype_info`	`sp_helplanguage`	`sp_releaseapplock`
sp_describe_first_result_set	`sp_helpsort`	`sp_rename`
sp_describe_undeclared_parameters	`sp_helpstats`	sp_settriggerorder (Windows Azure SQL Database)
`sp_executesql` (Some additional restrictions on batching statements together.)	`sp_helptext`	sp_tableoption (Windows Azure SQL Database)
`sp_getapplock`	`sp_helptrigger`	`sp_updatestats`
`sp_help`	`sp_recompile`	`sp_validname`

The following table lists the database engine stored procedures that Windows Azure SQL Database does not support:

`sp_add_data_file_recover_suspect_db`	`sp_detach_db`
`sp_add_log_file_recover_suspect_db`	`sp_dropdevice`
`sp_addextendedproc`	`sp_dropextendedproc`
`sp_addextendedproperty`	`sp_dropextendedproperty`
`sp_addmessage`	`sp_dropmessage`
`sp_addtype`	`sp_droptype`
`sp_addumpdevice`	`sp_getbindtoken`
`sp_altermessage`	`sp_helpdb`
`sp_attach_db`	`sp_helpdevice`
`sp_attach_single_file_db`	`sp_helpextendedproc`
`sp_bindefault`	`sp_helpfile`
`sp_bindrule`	`sp_helpfilegroup`
`sp_bindsession`	`sp_helpserver`
`sp_certify_removable`	`sp_indexoption`
`sp_clean_db_file_free_space`	`sp_dropdevice`
`sp_clean_db_free_space`	`sp_dropextendedproc`
`sp_configure`	`sp_dropextendedproperty`
`sp_control_plan_guide`	`sp_dropmessage`
`sp_create_plan_guide`	`sp_droptype`
`sp_create_plan_guide_from_handle`	`sp_getbindtoken`
`sp_create_removable`	`sp_invalidate_textptr`
`sp_cycle_errorlog`	`sp_lock`
`sp_dbcmptlevel`	`sp_monitor`
`sp_dbmmonitoraddmonitoring`	`sp_procoption`
`sp_dbmmonitorchangealert`	`sp_resetstatus`
`sp_dbmmonitordropmonitoring`	`sp_serveroption`
`sp_dbmmonitorresults`	`sp_setnetname`
`sp_dboption`	`sp_spaceused`
`sp_dbremove`	`sp_unbindefault`
`sp_delete_backuphistory`	`sp_unbindrule`
`sp_depends`	`sp_updateextendedproperty`
	`sp_who`

Security Stored Procedures

The security system stored procedures are used to manage security. Windows Azure SQL Database supports the following security stored procedures: sp_addrolemember, sp_droprolemember, and sp_helprole.

The following table lists the security stored procedures that Windows Azure SQL Database does not support:

`sp_addapprole`	`sp_dropalias`	`sp_helprotect`
`sp_addlogin`	`sp_dropapprole`	`sp_helpsrvrole`
`sp_addremotelogin`	`sp_droplogin`	`sp_helpsrvrolemember`
`sp_addrole`	`sp_dropremotelogin`	`sp_helpuser`
`sp_addserver`	`sp_droprole`	`sp_MShasdbaccess`
`sp_addsrvrolemember`	`sp_dropserver`	`sp_password`
`sp_adduser`	`sp_dropsrvrolemember`	`sp_remoteoption`
`sp_approlepassword`	`sp_dropuser`	`sp_revokedbaccess`
`sp_change_users_login`	`sp_grantdbaccess`	`sp_revokelogin`
`sp_changedbowner`	`sp_grantlogin`	`sp_setapprole`
`sp_changeobjectowner`	`sp_helpdbfixedrole`	`sp_srvrolepermission`
`sp_control_dbmasterkey_password`	`sp_helplinkedsrvlogin`	`sp_unsetapprole`
`sp_dbfixedrolepermission`	`sp_helplogins`	`sp_validatelogins`
`sp_defaultdb`	`sp_helpntgroup`	`sp_xp_cmdshell_proxy_account`
`sp_defaultlanguage`	`sp_helpremotelogin`
`sp_denylogin`	`sp_helprolemember`

Community Additions

ADD