Security with Stored procedures:
Different types of stored procedures in sql server:
- System Stored Procedures
2. Using output parameters
3. Using Try Catch
- Extended Stored Procedures
System Stored Procedures:
- A system stored procedures of sql are stored in the master database and these are starts with a sp_ prefix.
- This type of sql procedures can be used to perform variety of tasks to support sql server functions for external application in the system tables.
- For check use this syntax of query.
Some System procedures are Listed here:-
- sp_rename : It is used to rename an database object like stored procedure,views,table etc.
- sp_changeowner: It is used to change the owner of an database object.
- sp_help: It provides details on any database object.
- sp_helpdb: It provides the details of the databases defined in the Sql Server.
- sp_helptext: It provides the text of a stored procedure reside in Sql Server
- sp_depends: It provides the details of all database objects that depend on the specific database object
User Defined Stored Procedures:
- A User Defined stored procedures are usually stored in user database and use to complete the tasks in the user database.
- When the user Make these procedures don’t use sp_ prefix.
- When we use the sp_ prefix then it will check master database first then it comes to user defined database.
- Syntax of sql query for creating procedure.
Extended Stored Procedures:
- An Extended stored procedures that are use to call functions from DLL files.
- The extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.