quote:

---

Originally posted by James K

It may have to do with a broken ownership chain. Are the view and table owned by the same principal? This page can explain what I am talking about better than I can: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.90).aspx

If they are owned by different principals, you will need to grant select permission on the base table also. If they are in two databases, then again you will need to grant permissions on the base table unless you set up cross database ownership chaining (which you probably should not).

---

SELECT principal_id FROM sys.objects WHERE [name] = 'YourViewNameHere'
SELECT principal_id FROM sys.objects WHERE [name] = 'YourTableNameHere'

SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[VIEWS] WHERE TABLE_NAME = 'YourViewNameHere';
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[TABLES] WHERE TABLE_NAME = 'YourTableNameHere';

quote:

---

Originally posted by James K

Usually the owner is a schema. Run these two queries, they probably will return NULL.

SELECT principal_id FROM sys.objects WHERE [name] = 'YourViewNameHere'
SELECT principal_id FROM sys.objects WHERE [name] = 'YourTableNameHere'

If they return null, run this:

SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[VIEWS] WHERE TABLE_NAME = 'YourViewNameHere';
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[TABLES] WHERE TABLE_NAME = 'YourTableNameHere';

That will tell you which schema owns the objects.

---

CREATE TABLE dbo.TestTable(id INT);
GO
CREATE VIEW dbo.TestView AS SELECT id FROM dbo.tempTable;
GO
GRANT SELECT ON [dbo].[TestTable] TO [LessPrivilegedUser]
GO
INSERT INTO dbo.tempTable VALUES (1);

-- Connect as the [LessPrivilegedUser]
SELECT * FROM dbo.TestView;

CREATE TABLE dbo.TestTable(id INT);
GO
CREATE VIEW dbo.TestView AS SELECT id FROM dbo.TestTable;
GO
GRANT SELECT ON [dbo].[TestView] TO [LessPrivilegedUser]
GO
INSERT INTO dbo.TestTable VALUES (1);

-- Connect as the [LessPrivilegedUser]
SELECT * FROM dbo.TestView;