Urgent: Permissions Problem with Dynamic SQL

From: Mike Wade (mike.wade@doh.gsi.gov.uk)
Date: 08/14/02 

From: "Mike Wade" <mike.wade@doh.gsi.gov.uk>
Date: Wed, 14 Aug 2002 03:57:26 -0700

*** 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: Understanding of permissions
    ... There is a dynamic SQL statement in the proc ... The only time the user would need separate permissions on the underlying ... tables is if you are using dynamic SQL through either sp_executesql or EXEC ...
    (microsoft.public.sqlserver.security)
  • Re: SP accessing other db
    ... > so dynamic SQL always breaks the ownership chain. ... > The larger issue is why you need to use multiple databases. ... > chain security so that permissions on indirectly referenced objects are ...
    (microsoft.public.sqlserver.security)
  • Re: broken ownership chains
    ... only EXEC permissions on the stored procedure, ... on all the database objects referenced within the dynamic SQL. ... > insert into OnlyNumbers values ...
    (microsoft.public.sqlserver.security)
  • Re: Cannot perform BULK INSERT even though the account is a bulkadmin
    ... When you use dynamic sql, all object permissions are checked ... BULK INSERT command seems to be setting some SET option ... > We have a stored procedure that dynamically constructs a BULK INSERT ...
    (microsoft.public.sqlserver.security)
  • Re: sa privileges and roles
    ... permissions of that account. ... Another option is to create a dbo-owned user proc in an sa-owned user ... in your user database and grant execute permissions only on your user proc. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)