Please don't use the UI for this. It's a confusing mess.
It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created:
USE your_db;
GO
CREATE USER username FROM LOGIN username;
GO
GRANT SELECT ON dbo.MyViewName TO username;
GO
EDIT here is an example of a script that will lead to the error you mention.
First, create some table in the unrelated_db:
CREATE DATABASE unrelated_db;
GO
USE unrelated_db;
GO
CREATE TABLE dbo.foo(bar INT);
GO
Now create a relatively restricted login:
USE [master];
GO
CREATE LOGIN username WITH PASSWORD='foo', CHECK_POLICY = OFF;
GO
Now create a database where the view will live, and add the login as a user:
CREATE DATABASE velojason;
GO
USE velojason;
GO
CREATE USER username FROM LOGIN username;
GO
Now create a function that will reference the table in the other database, and a synonym to the other table:
CREATE FUNCTION dbo.checkbar()
RETURNS INT
AS
BEGIN
RETURN
(
SELECT TOP (1) bar
FROM unrelated_db.dbo.foo
ORDER BY bar
);
END
GO
CREATE SYNONYM dbo.foo FOR unrelated_db.dbo.foo;
GO
Now create a local table:
CREATE TABLE dbo.PaymentDetails
(
PaymentID INT
);
GO
Now create a view that references the table, the function and the synonym, and grant SELECT
to username
:
CREATE VIEW dbo.SomeView
AS
SELECT
p.PaymentID,
x = dbo.checkbar(), -- function that pulls from other DB
y = (SELECT bar FROM dbo.foo) -- synonym to other DB
FROM dbo.PaymentDetails AS p;
GO
GRANT SELECT ON dbo.SomeView TO username;
GO
Now try to execute as username
and select only the local column from the view:
EXECUTE AS USER = 'username';
GO
-- even though I don't reference any of the columns
-- in the other DB, I am denied SELECT on the view:
SELECT PaymentID FROM dbo.SomeView;
GO
REVERT;
GO
Result:
Msg 916, Level 14, State 1, Line 3
The server principal "username" is
not able to access the database "unrelated_db" under the current
security context.
Now change the view to not reference any external objects, and run the above SELECT
again, and it works:
ALTER VIEW dbo.SomeView
AS
SELECT
p.PaymentID
--x = dbo.checkbar(),
--y = (SELECT bar FROM dbo.foo)
FROM dbo.PaymentDetails AS p;
GO
Short of showing us the scripts for the Payment Details, Account Details and MyView objects, maybe you can let us know if this query returns any results. You can find references to various objects through the catalog view sys.sql_expression_dependencies
, but this view is not perfect - I believe it depends on all the views being refreshed (in the case where views reference other views, for example, or underlying schema has changed) in order to be accurate.
DECLARE
@dbname SYSNAME = N'unrelated_db',
@viewname SYSNAME = N'dbo.SomeView';
SELECT DISTINCT
[This object] =
OBJECT_SCHEMA_NAME([referencing_id])
+ '.' + OBJECT_NAME([referencing_id]),
[references this object] =
OBJECT_SCHEMA_NAME([referenced_id])
+ '.' + OBJECT_NAME([referenced_id]),
[and touches this database] = referenced_database_name,
[and is a(n)] = o.type_desc,
[if synonym, it references] = s.base_object_name
FROM sys.sql_expression_dependencies AS d
LEFT OUTER JOIN sys.objects AS o
ON o.[object_id] = d.referenced_id
LEFT OUTER JOIN sys.synonyms AS s
ON d.referenced_id = s.[object_id]
AND s.base_object_name LIKE '%[' + @dbname + ']%'
WHERE OBJECT_ID(@viewname) IN (
referenced_id,
referencing_id,
(SELECT referencing_id FROM sys.sql_expression_dependencies
WHERE referenced_database_name = @dbname)
) OR referenced_database_name = @dbname;
SQL Server isn't just going to try to access unrelated_db
for the fun of it... there must be some tie to that database from the view you're trying to use. Unfortunately if we can't see the view definition and more details about the objects it touches, all we can do is speculate. The two main things I can think of are synonyms or functions that use three-part names, but seeing the actual scripts will give us a much better idea instead of guessing. :-)
You may also want to check sys.dm_sql_referenced_entities
, however this function returns nothing useful in the example above.
With a bit of trial and error, I was able to spot the culprit. Turns out, it has nothing to do with the view itself. Attempting to manually push the where clause directly into the query also gave the same bad results.
As it turns out, the problem was cause by the CONNECT BY clause in the inner queries. My suspicion (which may be completely wrong) is that it has to do with the fact that Oracle 10g cannot handle recursive CTE's. If anyone has any more information about why this might have happened, I am all ears.
I was able to work around the issue by moving the
SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3
subquery to its own CTE block and selecting from that as normal. Below I have included the (condensed) working version of the above broken query.
CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS_2
(...)
AS
WITH
--Queries to generate intermediate result sets
--'NUMBERS' CTE block to work around Oracle 10g limitation wherein WHERE
-- clause is ignored if CONNECY BY is directly written into inner queries.
-- NOTE: the size limitation (currently 10) only needs to be larger than
-- or equal to the largest value needed.
NUMBERS AS (
SELECT level AS UNPIVOT_ROW
FROM DUAL CONNECT BY level <=10
),
--Distribution TRA
DIST_TRA AS (
SELECT ...
FROM (
SELECT ...
FROM FSA.FSA001_DISTRIBUT T1
INNER JOIN
FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
GROUP BY GROUPING SETS (...)
),(
SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 3 -- <== THIS
)
),
--Distribution Fran
DIST_FRAN AS (
SELECT ...
FROM (
SELECT ...
FROM FSA.FSA001_DISTRIBUT T1
INNER JOIN
FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
GROUP BY GROUPING SETS (...)
),(
SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 2 -- <== THIS AS WELL
)
)
--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT ...
FROM (SELECT * FROM DIST_TRA
UNION ALL
SELECT * FROM DIST_FRAN)
Best Answer
It's a pain, but Teradata doesn't support CTE in views (as of 15.00), see SQL Data Manipulation Language > The SELECT Statement > WITH and WITH RECURSIVE Statement Modifiers.
In your case you can create another view with the contents of the CTE, but you probably know that already.