stored proc permissions 1

[codestorm]

I know this should be possible, I just can't find how to do it (in a short time frame)!

I want to create a user for a database which has no permissions to create tables/etc and also has no permissions to select/insert/etc frm any tables.
This user will, however have permissions to stored procedures which can do the above.

e.g.
-User test1 has no permissions to create tables on database abc.
-User test1 has permission to execute stored procedure xyz on database abc.
-procedure xyz has permission to create tables on database abc.

Possible? <insert witticism here>
codestorm

 

[balves]

If the stored proc is created/owned by dbo and you give a user permissions to the stored proc, then you're all set...no?

 

[balves]

Of course, the objects referenced in the stored proc also must be owned by dbo.

To quote from BOL:

When a user accesses a view, Microsoft® SQL Server™ does not check permissions on any of the view's underlying objects if these objects and the view are all owned by the same user, and if the view and all its underlying objects are in the same database. If the same user owns a stored procedure and all the views or tables it references, and if the procedure and objects are all in the same database, SQL Server checks only the permissions on the procedure.

 

[codestorm]

Unfortunately I tried the above, and it still gives the error

CREATE TABLE permission denied in database 'bh_test'.

I am trying to run a simple proc which creates a table given a name. The proc is owned by dbo, and I'm logged in as someone with public permissions, and speicific permissions to run that proc.
<insert witticism here>
codestorm

 

[balves]

You're doing this kind of thing in the stored proc?

exec ('CREATE TABLE ' + @p_tablename ....)

 

[codestorm]

Yes. I realise it doesn't precompile such procs, I just made up that proc as something to test. <insert witticism here>
codestorm

 

[balves]

I think that falls outside of the &quot;ownership chain&quot; described in the BOL.

A CREATE TABLE statement right in the stored proc should work fine, although that doesn't appear to be what you want to do.

 

[codestorm]

Most of what I will be wanting to do will involve select/insert/update/delete, but (I'm working with someone else's 'evolved' system) one part of the system involves creating a new table for each new questionnaire posted.

Currently it's done in the ASP page by a SQL statement built there, but I'd rather move it to a SQL Server stored procedure.

Currently the web site(s) all use one login to access SQL Server, so I was just pondering tidying up SQL Server security by only allowing this login to run stored procs - no direct access to tables, etc.

However the creating custom tables one stumped me.
(Not to mention I've got to wade through all the sites' ASP pages to convert to SPs where I can)

*sigh* <insert witticism here>
codestorm

 

[RickCole]

Without knowing your system I could be barking up the wrong tree but...

As a work around and possibly even a better data design could you not have the questionnaire data as rows in a table or tables.

Rick.

 

[codestorm]

Sure could, but that's currently a mammoth task and not one which they're sparing me the time to do (believe me it was one of the first things I suggested) <insert witticism here>
codestorm

 

[RickCole]

What you could do as a work around is to work on something like the following:

Linked servers can be defined to point back to the themselves which MS define as a Loopback Linked Server

Take the following scenario

Your server is called NT01 and has a database called abc.

Set up a Login say called LNK using SQL Server authentication with an appropriate DBA password.

Add this login to database abc with the ability to create tables.

Run the following SQL to link the server to itself

EXEC sp_addlinkedserver @server = N'NT01_LINK',
@srvproduct = N' ',
@datasrc = N'NT01',
@catalog = N'abc'

Where @server is the logical name you wish to call your linked server and @datasrc is the actual name of the server.


Run the following SQL to add a linked server login

EXEC sp_addlinkedsrvlogin 'NT01_LINK', 'false', NULL, 'LNK', 'Password'

Where NT01_LINK is the logical name of your linked server. LNK is the login which local users will be mapped through and Password is the appropriate DBA password you supplied earlier to login LNK.

Now create the stored procedure, which creates your users tables i.e.

CREATE PROCEDURE user_test
AS
create table dbo.test (col_1 int null)

Ensure the user LNK in database abc has execute permissions to your create table stored procedure i.e. user_test.

From your code execute the create table stored procedure with a fully qualified name i.e.

exec NT01_LINK.abc.dbo.user_test

Users will be going through the mapped user LNK when executing this stored procedure (which is secure with a DBA password) to the database abc. They will not be able to create any tables within the database under there own login.

The above scenario I'm pretty sure will work and I think I’ve put everything on here as to how to work round the problem.

One word of caution is the following: Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction raises an error:

Rick