Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Finding Code Smells Using SQL Prompt: Procedures That Lack Schema-Qualification

DZone's Guide to

Finding Code Smells Using SQL Prompt: Procedures That Lack Schema-Qualification

It's a good idea to get into the habit of qualifying the names of procedures with their schema to help ensure that your code gas no ambiguities that could cause problems.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically during development and testing work for occurrences of a stored procedure being called via the EXECUTE command without the schema being specified.

Even when you don't have to qualify the name of a stored procedure — that is, whenever the procedure is in your default schema — performance is slightly better if you specify the schema and it makes the code more intelligible to others, more consistent, and easier to refactor.

The complete name of any schema-based database object consists of up to four identifiers: the server name, database name, schema name, and object name. Only if you are calling a remote stored procedure would you need a fully qualified name consisting of all four identifiers. If you are calling a procedure in another database, you obviously need its database identifier in the name. Within a database, you only need the object name itself so long as the procedure is in the same schema. By specifying the schema, the database engine needs less searching to identify it. Even system stored procedures should be qualified with the "sys" schema name. When creating a stored procedure, as well, it is a good habit to always specify the parent schema.

Database object names aren't unique within a server, only within a schema; therefore, we need to add qualifiers when appropriate, such as the server name, database name, or schema name, in order to ensure that we can identify the procedure we wish to execute, unambiguously. By doing so, we avoid certain bugs, minimize the time the engine spends searching for the procedure, and help ensure that cached query plans for the procedures get reused.

Problems With Nonschema-Qualified Procedures

A nonschema-qualified procedure name will be either a one-part name with no qualifier or what looks like a two-part name (database and object) with a double-dot between them where the schema name is missing. In such cases, the database engine must search in several places to find the required procedure, in the following order:

  1. First, it searches the "sys" schema of the current database.
  2. Next, the engine searches the users' default schema, unless the procedure is nested within another one, in which case it will search the schema that contains the outer procedure.
  3. Finally, it searches the DBO schema in the current database.

Conversely, if we schema-qualify a procedure simply by prefixing its name with the name of its schema, then the database engine does not have to search several schemas to locate the object, just the one specified.

Nonschema-qualified procedures introduce unexpected ambiguity, which can cause a bug that is hard to resolve. The more nesting of procedure calls takes place, the more chance of error. This ambiguity can also prevent cached query plans being reused.

The best approach is to qualify procedure names and to make sure that if names are duplicated within a database, it is done intentionally.

Schema Qualification and System Stored Procedures

System stored procedures can be executed from any database, and Microsoft recommends that you qualify them with the sys schema, though nothing will break if you don't.

Names of system stored procedures always begin with the characters sp_, standing for "special," and these procedures are stored in the Resource database. They will appear in the sys schema of all user-defined databases in that instance of SQL Server. If you create a procedure in your database with the same name as a system stored procedure, your procedure will never execute no matter how you qualify it with a schema name. The database engine always searches in the sys schema first.

Duplicate Procedure Names in the Same Database

Procedures with the same name can coexist in the same database perfectly happily if they are in separate schemas — and there are legitimate reasons for doing this. For example, you might want different categories of users to execute different code with the same procedure call. In this case, each group of users would need to have a different default schema to store their version of the procedure. Users would have EXECUTE permissions only on their own schema and use ownership-chaining to access the data they need. Users would then need to specify the procedure without the schema to allow SQL Server to choose the stored procedure from the default schema of the user.

If you have duplicate procedure names, then it becomes even more important to schema-qualify any procedure calls that mustn't be ambiguous! If not, it can introduce almost undetectable bugs that are caused when the wrong synonymous procedure gets called.

How SQL Server Deals With Procedure Calls That Aren't Schema-Qualified

We can demonstrate how stored procedures are searched by creating several with the same name but in different schemas. Listing 1 simulates having three different users in the database: myself with god-like powers as DBO, and two other users called The_First_User and The_Second_User. We assign each user to different default schemas and give them a different version of our test procedure. We then see which version of the procedures they execute when they make procedure calls that aren't schema-qualified, and what happens when we delete one.

/*
  This script is a series batches that illustrate how the Database engine works out what you want when you don't specify the schema of a stored procedure. 
  The behavior can be quite valuable if you want the same code to be handled differently according to the default schema of the user.
  Even if you don't have duplicate procedure names, this code shows that the default schema is searched before the DBO schema. 
  */
  SET NOCOUNT ON
  IF Object_Id('dbo.Test_Procedure') IS NOT NULL DROP PROCEDURE dbo.Test_Procedure;
  GO
  -- firstly we create a procedure in the DBO schema 
  CREATE PROCEDURE dbo.Test_Procedure
    @param1 VARCHAR(100) = 'Hello %s', @param2 VARCHAR(100) OUTPUT
  AS
  SELECT @param2 =
    CURRENT_USER + ' is in the ' + Schema_Name() + ' schema, while '
    + Replace(Coalesce(@param1, ''), '%s', 'World');
  RETURN 0;
  GO
  --now we create a procedure with the same name in the MySchema schema
  IF Object_Id('MySchema.Test_Procedure') IS NOT NULL DROP PROCEDURE MySchema.Test_Procedure;
  GO
  --just in case it has been left over from last time
  IF EXISTS
    (SELECT schemas.name FROM sys.schemas WHERE schemas.name LIKE 'MySchema')
    DROP SCHEMA MySchema;
  GO
  --we first create the schema
  CREATE SCHEMA MySchema;
  GO
  --now we create the stored procedure in this schema with a different output just so we know which is executed
  CREATE PROCEDURE MySchema.Test_Procedure
    @param1 VARCHAR(100) = 'Hello %s', @param2 VARCHAR(100) OUTPUT
  AS --make the wording different so we can prove which version of the procedure was executed
  SELECT @param2 =
    CURRENT_USER + ' is in the ' + Schema_Name() + ' schema, while '
    + Replace(Coalesce(@param1, ''), '%s', 'Planet');
  RETURN 0;
  GO
  --now we create a procedure with the same name in the MyOtherSchema schema
  IF Object_Id('MyOtherSchema.Test_Procedure') IS NOT NULL
    DROP PROCEDURE MyOtherSchema.Test_Procedure;
  GO
  IF EXISTS
    (SELECT schemas.name FROM sys.schemas WHERE schemas.name LIKE 'MyOtherSchema')
    DROP SCHEMA MyOtherSchema;
  GO
  CREATE SCHEMA MyOtherSchema;
  GO
  ---and in this schema we place a third schema
  CREATE PROCEDURE MyOtherSchema.Test_Procedure
    @param1 VARCHAR(100) = 'Hello %s', @param2 VARCHAR(100) OUTPUT
  AS --make the wording different again, so we can prove which version of the procedure was executed
  SELECT @param2 =
    CURRENT_USER + ' is in the ' + Schema_Name() + ' schema, while '
    + Replace(Coalesce(@param1, ''), '%s', 'Back yard');
  RETURN 0;
  GO
  --Now we create a couple of test users whose default schemas are those we've just created
  IF Database_Principal_Id('The_First_User') IS NOT NULL DROP USER The_First_User;
  IF Database_Principal_Id('The_Second_User') IS NOT NULL DROP USER The_Second_User;
  IF Database_Principal_Id('OurPhonyUsers') IS NOT NULL DROP ROLE OurPhonyUsers;
  GO
  -- before creating the users we create a role that we can assign to them so that 
  --they can do stuff
  CREATE ROLE OurPhonyUsers;
  GRANT EXECUTE ON SCHEMA::MySchema TO OurPhonyUsers;
  GRANT EXECUTE ON SCHEMA::MyOtherSchema TO OurPhonyUsers;
  GRANT EXECUTE ON SCHEMA::dbo TO OurPhonyUsers; --comment this out to see what happens!
  GO
  --Now we create the users and attach them to the role we created
  CREATE USER The_First_User WITHOUT LOGIN WITH DEFAULT_SCHEMA = MySchema;
  EXEC sys.sp_addrolemember N'OurPhonyUsers', N'The_First_User';
  EXECUTE AS USER = 'The_First_User';
  GO
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as The_First_User
  EXECUTE Test_Procedure @param1 = 'firstly calling ''hi'' to the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  REVERT; --to being myself
  GO
  CREATE USER The_Second_User WITHOUT LOGIN
  WITH DEFAULT_SCHEMA = MyOtherSchema;
  EXEC sys.sp_addrolemember N'OurPhonyUsers', N'The_Second_User';
  EXECUTE AS USER = 'The_Second_User';
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as The_Second_User
  EXECUTE Test_Procedure @param1 = 'Secondly welcoming the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  REVERT; --to being myself
  GO
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as dbo
  EXECUTE Test_Procedure @param1 = 'thirdly acknowledging the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  SELECT 'now deleting the MyOtherSchema.Test_Procedure'
  --now we drop the procedure with the same name in the MyOtherSchema schema
  IF Object_Id('MyOtherSchema.Test_Procedure') IS NOT NULL
    DROP PROCEDURE MyOtherSchema.Test_Procedure;
  GO
  EXECUTE AS USER = 'The_Second_User';
  DECLARE @MyMessage VARCHAR(100);
  --execute the stored procedure without any qualifier as The_Second_User
  EXECUTE Test_Procedure @param1 = 'fourthly saying ''greetings'' to the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  --execute the stored procedure with a qualifier as The_Second_User
  EXECUTE MySchema.Test_Procedure @param1 = 'finally saying hello to the %s',
    @param2 = @MyMessage OUTPUT;
  SELECT @MyMessage;
  REVERT; --to being myself
  GO
  ---and clean up
  DROP PROCEDURE MySchema.Test_Procedure;
  DROP PROCEDURE dbo.Test_Procedure;
  DROP USER The_First_User;
  DROP USER The_Second_User;
  DROP ROLE OurPhonyUsers;
  DROP SCHEMA MyOtherSchema;
  DROP SCHEMA MySchema;

If executed, this will give the following results:

----------------------------------------------------------------------------------------
  The_First_User is in the MySchema schema, while firstly calling 'hi' to the Planet
  ----------------------------------------------------------------------------------------
  The_Second_User is in the MyOtherSchema schema, while Secondly welcoming the Back yard
  -----------------------------------------------------------------------------------------
  dbo is in the dbo schema, while thirdly acknowledging the World
  -----------------------------------------------------------------------------------------
  now deleting the MyOtherSchema.Test_Procedure
  -----------------------------------------------------------------------------------------
  The_Second_User is in the MyOtherSchema schema, while fourthly saying 'greetings' to the World
  -----------------------------------------------------------------------------------------
  The_Second_User is in the MyOtherSchema schema, while finally saying hello to the Planet

What do you make of this?

  • When the first user executes the stored procedure, Test_Procedure, it executes the version in MySchema because that is its default schema, and the database engine looked there next after the checking the sys schema.
  • The same happened to the second user, who was assigned to a different version of Test_Procedure that we put in the schema MyOtherSchema.
  • We reverted to me as the user, and because my default schema is DBO, I executed the first version of the procedure.
  • Next, we dropped the MySchema.Test_Procedure and the next time The_Second_User executed Test_Procedure without schema qualification, it was the one in DBO that was executed. That happened because if the database engine can't find the procedure in the user's default schema it looks in DBO for it.
  • Finally, The_Second_User is in MyOtherSchema, and executes the schema-qualified procedure and the correct version (MySchema.Test_Procedure) is executed.

Conclusions

It is a very good idea to get into the habit of qualifying the names of procedures with their schema. It does not only make your code more resilient and maintainable but also, as Microsoft introduces new features that use schemas such as auditing mechanisms, your code contains no ambiguities that could cause problems.

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
sql prompt ,database ,code analysis ,code smells ,sql ,schema ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}