By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,787 Members | 2,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,787 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Nov 21 '06 #1
Share this Question
Share on Google+
6 Replies


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

"Peter Nurse" <Pt****@yahoo.com.auwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.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.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.

Nov 21 '06 #2

P: n/a

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.

Nov 21 '06 #3

P: n/a
Hi
Grant EXECUTE permissions on stored procedure to the specific user


"Uri Dimant" <ur**@iscar.co.ilwrote in message
news:%2****************@TK2MSFTNGP02.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" <Pt****@yahoo.com.auwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.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.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.


Nov 21 '06 #4

P: n/a
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.

Nov 21 '06 #5

P: n/a
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" <Pt****@yahoo.com.auwrote in message
news:11**********************@m73g2000cwd.googlegr oups.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.

Nov 21 '06 #6

P: n/a
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!

Nov 21 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.