|
|
Posted by Uri Dimant on 11/21/06 05:24
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.com.au> wrote in message
news:1164086192.927936.34030@e3g2000cwe.googlegroups.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.
>
Navigation:
[Reply to this message]
|