|
|
Posted by Uri Dimant on 11/21/06 06:19
Hi
Grant EXECUTE permissions on stored procedure to the specific user
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%238qcV1SDHHA.3396@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" <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]
|