September 26, 2008 at 9:42 am
I added this same question to the "SQL Server 2005 Security" forum as well. However, the Administration group is much more active.
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 10:06 am
If you were to enable Cross-Database ownership chaining, you would be able to give them access just to DatabaseA and they could have access to DatabaseB tables if the VIEW in DatabaseA and the table in DatabaseB have the SAME owner. At the moment, that is not the case for your databases.
If you were to enable cross-database ownership chaining, you add some security risk because someone that is DBO in one database can create a view that can query another database they do not have access to.
Other than that, you need to give users access to the appropriate objects in DatabaseB. There is nothing built into MSSQL for giving access to a user for one application but not another - and since a user can manipulate the application name in a connection string it may not be a useful thing to be able to do anyway. I would recommend you create some views or stored procedures in DatabaseB that are as restrictive as possible and give the users access to these objects.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply