Post reply

proc that selects from other database need to be executed in current db

  • November 2, 2010 at 2:02 pm

    #228586

    Here is the case.

    On SQL 2005 server A,

    I have couple of databases. Let’s just talk about only 2 databases for now.

    1)Test –testuser (read, write, exec permissions on Test Database)

    2)Sample ---sampleuser

    Where Test and sample ==Databases

    Testuser and sampleuser = Database users.

    Exec permission means user can execute any Procedure. For that we have created a custom database role called proc_execute. Every Procedure must be a member of this role and any user in the database is also a member of this role then only they can execute these procedures.

    Now here is the SP that testuser needs to create in TEST Database,

    USE [TEST]

    Go

    CREATE PROCEDURE Usp_getsampledata

    AS

    SELECT * FROM [SAMPLE].[Dbo].[table1]

    GO

    GRANT EXECUTE ON [dbo].[Usp_getsampledata] TO [proc_execute] AS [dbo]

    GO

    Testuser has created this Procedure in Test DB. But When he tries to Execute it, he gets following error message,

    Msg 229, Level 14, State 5, Procedure Usp_getsampledata, Line 2

    The SELECT permission was denied on the object 'table1', database 'sample', schema 'dbo'.

    How Can I resolve this Problem?? Here are few ways that I can think but none of them is working for me, please see and advise,

    1)Grant read access (db_datareader) to testuser in sample database.But the drawback is now testuser can read data from all tables of sample database which I don’t want.

    2)If I use openrowset/opendatasource then also I need to supply username and password to access sample database, means passwords get hardcoded which I don’t want.

    3)Cross Database ownership enable for both test and sample databases but then any user from any db with cross db enabled can access my sample database which I don’t want.

    What is the minimum level of permission I can give to testuser?? Also this is not just one SP, we have number of procedures with same functionality that we need to implement so we want to design best security level from now only.

  • December 14, 2010 at 4:03 pm

    #1262582

    You could give 'Select' rights on the [SAMPLE].[Dbo].[table1] table to TestUser. In that case TestUser will have access only to that particular table.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply