Default Table Owner using CREATE TABLE, INSERT, SELECT & DROP TABLE

microsoft.public.sqlserver.programming

21 Nov 2006 5:16 AM

Peter Nurse

For reasons that are not relevant (though I explain them below *), I
want, for all my users whatever privelige level, an SP which creates
and inserts into a temporary table and then another SP which reads and
drops the same temporary table.

My users are not able to create dbo tables (eg dbo.tblTest), but are
permitted to create tables under their own user (eg MyUser.tblTest). I
have found that I can achieve my aim by using code like this . . .
        SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstID
DATETIME)'
        EXEC (@SQL)
        SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest
(tstID) VALUES(GETDATE())'
        EXEC (@SQL)

This becomes exceptionally cumbersome for the complex INSERT & SELECT
code. I'm looking for a simpler way.

Simplified down, I am looking for something like this . . .

        CREATE PROCEDURE dbo.TestInsert AS
                CREATE TABLE tblTest(tstID DATETIME)
                INSERT INTO tblTest(tstID) VALUES(GETDATE())

        GO

        CREATE PROCEDURE dbo.TestSelect AS
                SELECT * FROM tblTest
                DROP TABLE tblTest

In the above example, if the SPs are owned by dbo (as above), CREATE
TABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT use
dbo.tblTest.

If the SPs are owned by the user (eg MyUser.TestInsert), it works
correctly (MyUser.tblTest is used throughout) but I would have to have
a pair of SPs for each user.

* I have MS Access ADP front end linked to a SQL Server database. For
reports with complex datasets, it times out. Therefore it suit my
purposes to create a temporary table first and then to open the report
based on that temporary table.