Users Default Schema in a stored procedure.

• Michael L John

  One Orange Chip

  Points: 27079

  More actions

  June 6, 2025 at 6:09 pm

  #4603729

  One of our systems contains data for multiple organizations.  OrgA should not be able to see OrgB's data and so forth.

  The vendor for this system provided a set of views that filter the data for each organization.  Simply, there is a table dbo.TheTable. They created a view Organization.TheTable that does a SELECT * FROM dbo.TheTable. That is joined to another table that contains the different organizations, so the data is filtered depending upon the user.  Not the way I would have done it, but it is vendor supplied, and it works.  The default schema for the users is set explicitly to the schema that contains the views.

  The issue is the manner in which it is to be implemented.

  If a user executes this: SELECT * FROM database..TheTable, it works fine.  They see the data for their organization.

  This results in an error, as expected SELECT * FROM database.dbo.TheTable.

  However, if this syntax SELECT * FROM database..TheTable is executed INSIDE A PROCEDURE, it fails with the permission denied error.

  This works: SELECT * FROM database..TheTable

  This fails:

  CREATE PROCEDURE dbo.TheProc

  as

  SELECT * FROM database..TheTable

  GO

   

  • This topic was modified 1 month, 1 week ago by Michael L John.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• Ed B

  Hall of Fame

  Points: 3686

  More actions

  June 7, 2025 at 4:45 am

  #4603921

  I tried to reply, but it failed to post,  with an error "are you sure you want to do that". I think the issue is schema scope of stored procedures. The default schema is ignored and the procedure first looks for objects in the same schema as the procedure, then tries dbo.

  You could create every stored procedure in every schema and the problem would go away. Users would not need to specify a schema when executing a procedure as the default would be assumed. You could also take advantage of the schema scope and eliminate schema from the procedure code too, as it will be inferred from the procedure. This wouldn't save much effort, but it might feel like a small victory.