[AccessD] Granting permissions to new SQL Table

I thinky will find the problem is your use of dbo.

http://msdn.microsoft.com/en-us/library/aa905208%28v=sql.80%29.aspx

Do really want to create the temp table under dbo rather than under the user?

-- 
Stuart

On 30 Nov 2012 at 18:21, David Emerson wrote:

> Fellow listers,
> 
>  I
> 
> 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
> 
>  
> 
>  
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>