Post reply

User/Schema Access

  • September 26, 2008 at 9:36 am

    #198648

    I have a question related to using multiple databases, schemas, views, and overall security. Here it goes:

    I have two databases:

    DatabaseA

    Schema: PCI

    Schema Permissions: SELECT

    Login SQL: rptUser

    DB User: rptUser

    VIEW: CREATE VIEW PCI.Table1

    AS

    SELECT *

    FROM DatabaseB.dbo.Table1

    DatabaseB is the source database

    Schema: dbo

    Schema Permissions: SELECT

    My question:

    With Reporting Services, I need users to access DatabaseA and Select data. However, If they have SQL Server installed on there machine, I need to prevent Login Access to DatabaseB. How can I allow for SELECT access to both database, but prevent a user from accessing DatabaseB through non-reporting services tools i.e: Management Studio, OSQL, sqlcmd, etc...?

    Or, how can I allow for SELECT access to DatabaseA without granting database access to DatabaseB? If I don't allow access I get the error: "The server principal “rptUser” is not able to access the database “DatabaseB” under the current security context."

    Thanks

    Greg

  • September 26, 2008 at 2:51 pm

    #877974

    you could write a stored procedure that does you select and give them execute on that stored procedure. That way they can do their select without having permissions on the underlying data.

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

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