Re: Urgent: Permissions Problem with Dynamic SQL

From: Andrew J. Kelly (akelly@targitinteractive.com)
Date: 08/14/02 

From: "Andrew J. Kelly" <akelly@targitinteractive.com>
Date: Wed, 14 Aug 2002 08:07:27 -0400

Mike,

I believe this was answered in .programming. You can post to more than one
NG by simply adding in the other NG's in the "Newsgroups" section of the
post. This way they will all be linked so when an answer is posted in one
NG it shows up in all the others as well. 

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.
"Mike Wade" <mike.wade@doh.gsi.gov.uk> wrote in message
news:24a901c24381$608d4b70$3bef2ecf@TKMSFTNGXA10...
> *** Also Posted in  Programming ***
>
> Hi,
>
> I have 2 databases residing on the same server and an
> having problems with permissions when using dynamic sql.
> The first database is a sort of Portal to numerious data
> sources.  It contains all user accessible stored procs,
> etc.  The second contains source data.
>
> The problem I have is (for example):
>
> USE db1
> GO
> CREATE PROC a AS
>    BEGIN
>    SELECT * FROM db2.dbo.Table1
>    END
> GO
> GRANT EXECUTE ON a TO user1
> GO
> EXECUTE a  --  ****** THIS WORKS OK
> GO
>
>
> USE db1
> GO
> CREATE PROC a2 AS
>    BEGIN
>    DECLARE @Sql VARCHAR(8000)
>    SET @Sql ='SELECT * FROM db2.dbo.Table1'
>    EXEC (@Sql)
>    END
> GO
> GRANT EXECUTE ON a2 TO user1
> GO
> EXECUTE a2  --  ****** THIS DOES NOT WORK
> /* Reports Permission Denied on Table1 */
> GO
>
> USE db1
> GO
> CREATE VIEW v AS
>    SELECT * FROM db2.dbo.Table1
>
> CREATE PROC a3 AS
>    BEGIN
>    DECLARE @Sql varchar(8000)
>    SET @Sql='SELECT * FROM v'
>    EXEC (@Sql)
>    END
> GO
> GRANT EXECUTE ON a3 TO user1
> GO
> EXECUTE a3  --  ****** THIS WORKS OK
> GO
>
>
>
> In all cases user1 is a user of both db1 and db2 but in
> db2 the user is in the public group only and no express
> permissions have been granted (or revoked) to the user or
> public group.
>
>
> The core problem is that I need a work around to the
> procedure a2 which is functionally equivelent (ie the
> dynamic slq is built in the stored proc), but do not want
> to open up db2 to abuse by granting express select
> permissions to tables (as some of the data is sensative).
>
>
> Any Ideas?
>
>

Relevant Pages

  • Re: Global Temp Table & Multiple Users
    ... design and I'm not sure if performance would be any better. ... about serialization is that it limits the dbms pounding that these reports ... what is happening is this a dynamic SQL ... > The inner proc generates a portion of the SQL, ...
    (microsoft.public.sqlserver.server)
  • Re: Variable server names
    ... My initial desire was to do this in a single stored proc. ... Then I realized that mixing dynamic SQL and a table variable was going ... executing the remote from the local. ... but I understood it as management wanted to connect to a server ...
    (microsoft.public.sqlserver.programming)
  • Re: Views vs Stored Procedures, whats the difference?
    ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
  • Re: how to pass an sort by parameter to a stored proc
    ... When executing a string the are not optional. ... Also if security is of any concern, this proc is very exposed to SQL ... >> CREATE PROCEDURE spListUsers ...
    (microsoft.public.sqlserver)
  • Re: Reset permissions
    ... undocumented APIs in production code. ... I recommend building some simple code generators like the one shown below. ... GRANT EXECUTE ON SCHEMA:: someschema TO someuser ... SQL Server Engine ...
    (microsoft.public.sqlserver.security)