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

[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.TestInse rt), 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.

[Uri Dimant]

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

Peter
What is the version are you using? SQL Server 2005 has a great feature
EXECUTE AS for such kind of requirements



"Peter Nurse" <PtrNrs@yahoo.c om.auwrote in message
news:1164086192 .927936.34030@e 3g2000cwe.googl egroups.com...

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.TestInse rt), 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.
>

[Peter Nurse]

Re: Default Table Owner using CREATE TABLE, INSERT, SELECT &amp; DROP TABLE


Uri Dimant wrote:

Peter
What is the version are you using? SQL Server 2005 has a great feature
EXECUTE AS for such kind of requirements

SS 2K unfortunately.

[Uri Dimant]

Re: Default Table Owner using CREATE TABLE, INSERT, SELECT &amp; DROP TABLE

Hi
Grant EXECUTE permissions on stored procedure to the specific user






"Uri Dimant" <urid@iscar.co. ilwrote in message
news:%238qcV1SD HHA.3396@TK2MSF TNGP02.phx.gbl. ..

Peter
What is the version are you using? SQL Server 2005 has a great feature
EXECUTE AS for such kind of requirements
>
>
>
"Peter Nurse" <PtrNrs@yahoo.c om.auwrote in message
news:1164086192 .927936.34030@e 3g2000cwe.googl egroups.com...

>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.TestInse rt), 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.
>>

>
>

[Peter Nurse]

Re: Default Table Owner using CREATE TABLE, INSERT, SELECT &amp; DROP TABLE

Uri Dimant wrote:

Hi
Grant EXECUTE permissions on stored procedure to the specific user

Thanks Uri,

Yes, all users already have EXECUTE permission - that's not the
problem. Remember that in the original post, I said . . .

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.

.. . . that refers to the behaviour when executed by a non-dbo (shall we
call them underprivileged ?) user.

[Uri Dimant]

Re: Default Table Owner using CREATE TABLE, INSERT, SELECT &amp; DROP TABLE

Peter
BOL says
Database Owner (dbo)
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.

For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):

a.. Belong to the user creating the object, not dbo.


b.. Are qualified with the name of the user who created the object.




"Peter Nurse" <PtrNrs@yahoo.c om.auwrote in message
news:1164090859 .549657.261440@ m73g2000cwd.goo glegroups.com.. .

Uri Dimant wrote:

>Hi
>Grant EXECUTE permissions on stored procedure to the specific user

>
Thanks Uri,
>
Yes, all users already have EXECUTE permission - that's not the
problem. Remember that in the original post, I said . . .
>
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.
>
. . . that refers to the behaviour when executed by a non-dbo (shall we
call them underprivileged ?) user.
>

[Peter Nurse]

Re: Default Table Owner using CREATE TABLE, INSERT, SELECT &amp; DROP TABLE

Thanks, Uri.

I think what you're saying (and I'm discovering!) is that I'm stuck
with my (perceived) problem.

A friend has made the best suggestion yet:-

How about using a single table with guid, username or SPID
to provide that separation?

Then it becomes a simple delete/insert to update the data.
As an added bonus having a permanent table means that
you can take advantage of all the good things like indexing and
statistics.

Thanks for your help!