SQL SERVER – BEWARE OF USING PREFIX “SP_” IN USER DEFINED STORED PROCEDURE

During the Analysis of a database, I found some procedure names starts with “sp_” .This is a small mistake, but mistake is mistake and we have to overcome it.This will reduce the performance of your  SQL Server . Most of the people in IT Industry will simply ignore this statement because what makes a performance issue by naming a procedure using “SP_” ?. But the truth is it will reduce the performance a little bit. Let’s look into it,

  • What happens if procedure name starts with “SP_” in SQL Server?

While executing the Stored Procedure, the SQL Server Engine will First search for the system stored Procedure starting with “SP_” in the master databases. After completing this search only, it will consider the User defined Stored Procedure. By changing the naming convention to something like “USP_” will  improve the Performance of the database. Also, if there happens to be a system stored procedure with an identical name, your procedure won’t get processed at all.

  • Conclusion:

SQL Server Performance relies on various factors. Here we see a simple naming convention can improve the performance. Design the database using Good Experts. Otherwise you have to pay for Performance.

SQL SERVER – OWNERSHIP CHAINING – A Security Feature

What is an ownership chaining?

            Each object within a database has an owner associated with it i.e. schema owner. You can also build objects that reference other objects within a database, such as SPs that call functions which issue SELECT statements against the views that are based on the tables. The owner of the each object that is referenced in a calling stack forms an ownership chain as the code transits from one object to the next within the calling stack. So long as the owner  of the object and any other objects that it references have the same owner , you have an intact ownership chain.SQL SERVER checks your permissions on an object at the top of the calling stack , as well as each time the object owner changes within a calling stack.

Test scenario:

Consider the table   Testtable  contained in the schema called Mainschema which is owned by a user ‘mainuser’. we can create a view named vw_test in another schema called ‘interschema’ with another user called ‘interuser’ . we are giving acess to the application using an SP who is contained in another schema called ‘appschema’ and user owns the schema is  ‘appuser’ . The SP is selecting the data from the vw_test.

The steps are as follows,

  • Create  three users without login and add role db_owner to user Mainuser

               

  • Create three schemas and authorize the users to access the schema.

  • The below script shows the schemas and their owners

.

  • Create a table Testtable in the Mainschema
  •  

     

     

     

     

     

     

     

    • Create a view vw_test under theschema“interschema”. We also granting acess  to the Mainschema.Testtable to interuser.

  • Create a procedure  under the schema appschema.Grant select on Interschema.vw_test to Appuser
  • Execute using the SP  appuser
  • Ownership chain works in the above script.The appuser have only acess to the SP not to the undelying tables or views.This is a great security feature in sql server which is introduced in SQL SERVER 2005.I have done these test scenarios in the SQL SERVER 2008.Hope every one likes this article.

    ANURAJ.C.R AN ENTHUSIASTIC DBA

    Dear guys, Today I will introduce a new guy Anuraj c.r who is a DevelopmentDBA who is working in 365 media.He is my close friend.we studied in the same college and now also we maintain a good relationship.He is an Expert in T-SQL coding.He has some good business ideas.He always thinks about 100% perfection,but it is not possible in the real world.But he is always trying for 100% perfection(that make him different).When he had some doubts he will make a call and I will try to clear his doubts.Now he is interested in Production Administration and he s moving into this area.we together solved so many issues in SQL SERVER.He is very enthusiastic.Any way he is  a nice guy.I am happy to say that he is a good friend of me for ever.