[AccessD] Granting permissions to new SQL Table

Thanks Stuart.  Much appreciated

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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com