[AccessD] Granting permissions to new SQL Table

I was using DBO for convenience as once the tables are created they are used
in other stored procedures by the same user as the source for reports.  If I
leave dbo off and just refer to the table name, will other procedures that
also just use the table name (which would not be prefixed with dbo) know
which table to use?
David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, 30 November 2012 8:00 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [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 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