How To Grant Permissions For a Given SQL User Id To Use the 'dbo' Schema

Greetings;

I have a stored procedure which includes:

      IF OBJECT_ID('dbo.SelectedCompaniesInitial') IS NOT NULL
            DROP      TABLE dbo.SelectedCompaniesInitial

I believe I am receiving the message:

Msg 2760, Level 16, State 1, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.

when this part of the stored procedure attempts to execute.

How do I give permissions for the given user to 'use' the schema name 'dbo'?

Much thanks ... David

The entire stored procedure follows:

USE [ACCMACompanyRegistry]
GO
/****** Object:  StoredProcedure [dbo].[sp_SearchCompanyByCategory]    Script Date: 02/21/2008 13:45:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Bach & Bach Web Solutions, Inc.
-- Create date: 2/15/2008
-- Description:      Retrieve company rows for Company Registry search
--              matching 1 or more selected categories
-- =============================================
ALTER PROCEDURE [dbo].[sp_SearchCompanyByCategory]
      -- Add the parameters for the stored procedure here
      @strCompanyCategoryWHERE      varchar(1000)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      IF OBJECT_ID('dbo.SelectedCompaniesInitial') IS NOT NULL
            DROP      TABLE dbo.SelectedCompaniesInitial

      EXEC
            ('SELECT            DISTINCT strCompany
                  INTO             dbo.SelectedCompaniesInitial
                  FROM            dbo.Specialty_MM_Company
                                          INNER JOIN dbo.Company
                                                INNER JOIN dbo.Contact
                                                      ON dbo.Company.lngID = dbo.Contact.fkCompany_lngId
                                                      ON dbo.Specialty_MM_Company.fkCompany_lngId = dbo.Company.lngID
                                                      INNER JOIN dbo.Specialty_Category
                                                            INNER JOIN dbo.Specialty
                                                                  ON dbo.Specialty_Category.strCategory = dbo.Specialty.fkSpecialty_Category_strCategory
                                                                  ON dbo.Specialty_MM_Company.fkSpecialty_lngId = dbo.Specialty.lngId
                  WHERE ' + @strCompanyCategoryWHERE)

            SELECT            Company.lngId,
                              Company.strCompany,
                              Company.strAddress_1,
                              Company.strAddress_2,
                              Company.strCity,
                              Company.strState,
                              Company.strZIp,
                              Company.blnDBE,
                              Company.blnLBE,
                              Company.blnSBE,
                              Company.dtmCreated,
                              Company.dtmUpdated
                  INTO            #SelectedCompaniesFinal
                  FROM            Company
                                          JOIN SelectedCompaniesInitial
                                                ON Company.strCompany = SelectedCompaniesInitial.strCompany

      DROP      TABLE dbo.SelectedCompaniesInitial

      SELECT            #SelectedCompaniesFinal.strCompany,
                        #SelectedCompaniesFinal.strAddress_1,
                        #SelectedCompaniesFinal.strAddress_2,
                        #SelectedCompaniesFinal.strCity,
                        #SelectedCompaniesFinal.strState,
                        #SelectedCompaniesFinal.strZIp,
                        #SelectedCompaniesFinal.blnDBE,
                        #SelectedCompaniesFinal.blnLBE,
                        #SelectedCompaniesFinal.blnSBE,
                        #SelectedCompaniesFinal.dtmCreated,
                        #SelectedCompaniesFinal.dtmUpdated,
                        Contact.strEmail
            FROM            #SelectedCompaniesFinal
                                    LEFT OUTER JOIN Contact
                                          ON #SelectedCompaniesFinal.lngID = Contact.fkCompany_lngId
            WHERE            Contact.blnEmailOk = 1 AND
                              Contact.blnCompany_Admin = 1
            ORDER BY      strCompany

END
Start Free Trial