Post reply

Using a database as a repository to procedures that use other databases

  • January 22, 2013 at 8:09 am

    #288283

    I have a database that is used as a repository of all stored procedures that are available to the application. Currently, the application user has access to all databases that the stored procedures manipulate but we want to withdraw those rights and only give them rights to execute those procedures from the repository. Unfortunately, when the application login has its rights to the other databases removed, the stored procedures no longer work. Is there any way to assign rights to those stored procedures to allow the user to execute them without granting them direct access to the databases where the data resides? We are using SQL Server 2005 Standard Edition.

    Thanks,

    Eduardo

  • January 22, 2013 at 9:43 am

    #1579717

    nope; if the procedure exists in a different database, then the login needs a matching user mapped to it in each of those databases;

    that user in the "procedure" database would be in a group that has access to only those procs you are referring to, doesn't need anything but EXECUTE rights on those.

    since you are tightening security, you obviously don't want to grant execute to the PUBLIC group as the lazy work around, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 22, 2013 at 9:45 am

    #1579720

    Thanks, that's what I thought but my users insist there is a way other than that. It would save them coding time but... that would be only if that is possible.

  • January 23, 2013 at 4:06 am

    #1579990

    I have found a way that I am currently exploring by using certificates. It is explained in the "Cross Database Access" section of the following article:

    http://www.sommarskog.se/grantperm.html

  • January 23, 2013 at 12:52 pm

    #1580240

    But I have found a problem with table value functions and views that go across databases. When trying to ADD SIGNATURE, I get the following error:

    Msg 15560, Level 16, State 1, Line 1

    Cannot add or drop a signature on 'dbo.table function or view' because only modules can be signed.

    Does anyone know a way around it?

  • January 25, 2013 at 3:23 am

    #1580914

    I am trying to achieve that by replacing those tables with viewes in the other database and granting SELECT access to the common login. That should work but time will tell.

Viewing 6 posts - 1 through 5 (of 5 total)

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