February 3, 2010 at 9:20 am
I have a Sql Server 2005 database that we use for a new web app. Let's call it DB1 which is used for the web. I have another database that is on the same instance of sql server 2005 that we use for our internal application. Let's call it DB2, which is used for our internal app. I have created a view on DB1 that has a view of a table on DB2
I'm logged in with an id that has permissions to both database DB1 and DB2 and have perfect access.
However, when I use the website security log on, I get the error: Unable to get records. The server principal "user" is not able to access the database "DB2" under the current security context.
How do I grant permission to the "user" ID of DB1 to the DB1 view which is of a DB2 table?
February 3, 2010 at 9:39 am
You might want to give this article a read.
February 3, 2010 at 11:06 am
Does anyone see any problems to me giving the "User" ID of DB1 permissions in user mappings to DB2 with "db_datareader" permissions?
It seems to work, but wonder what doors I might be opening up.
February 3, 2010 at 1:15 pm
By giving that access, anyone who know the web sites username/password now has access to all of the data in your internal apps database.
If there is any chance that you are subject to a SQL injection attack, it would be possible for someone outside your organization to get access to internal data.
Only you can decide whether or not the risks are acceptable.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 3, 2010 at 1:51 pm
Do you have a suggestion of how I should do it to not open myself up to sql injection attacks?
February 3, 2010 at 1:56 pm
Have you looked at using EXECUTE AS so the procedure runs in the context of a privileged user?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 3, 2010 at 2:03 pm
I found something about EXECUTE, but when I tried it, it told me I was trying to impersonate the user.
Maybe I was doing something incorrectly.
Can you suggest where I would add the EXECUTE statement?
USE [DB1]
GO
/****** Object: View [dbo].[Users] Script Date: 02/03/2010 16:01:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Users]
AS
SELECT DISTINCT Username, Password, FirstName, LastName, Email
FROM DB2.dbo.userlist AS c
WHERE (StatusCode = 1)
February 3, 2010 at 2:08 pm
Instead of using a view - create a stored procedure and use EXECUTE AS on the procedure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply