[AccessD] Granting permissions to new SQL Table

Fellow listers,

 

I have an SQL 2012 R2 database with the following in a stored procedure:

 

      IF EXISTS(SELECT table_name FROM information_schema.tables WHERE
table_name = 'ttmpFaultGraphReprocessm') 

         DROP TABLE ttmpFaultGraphReprocessm

 

      CREATE TABLE dbo.ttmpFaultGraphReprocessm (Department varchar(50))

      GRANT SELECT, INSERT, UPDATE ON [dbo].[ttmpFaultGraphReprocessm] TO
[CompanyUser]

 

When I (as the database owner) run the sproc it works as expected.  When any
user (who is a member of the CompanyUser role) tries to run it the table is
created but the sproc stops at the Grant select line with the error "Cannot
find the object 'ttmpFaultGraphReprocessm', because it does not exist or you
do not have permission.  If I go into SQL I can see the table created but
there are no permissions set.

 

What permissions does the CompanyUser role need to have to be able to grant
permissions to the table?  It needs to be recreated because further down the
sproc adds further columns based on other data.

 

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand