[AccessD] Granting permissions to new SQL Table

Yes.  Next page after the previous link:

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

<quote>
 If an object is not qualified with the object owner when it is referenced (for example, 
my_table instead of owner.my_table), SQL Server looks for an object in the database in the 
following order:

    1. Owned by the current user.
    2. Owned by dbo.
</quote>

-- 
Stuart

On 30 Nov 2012 at 20:19, David Emerson wrote:

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