SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 View base table permission problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

angelran
Starting Member

7 Posts

Posted - 02/22/2013 :  07:54:32  Show Profile  Reply with Quote
Hi guys,

I'm new here and seeking for some help.

I created a new user in DB and granted "select" permission on a view. when I run the view as that user no row was returned unless I granted "select" permisson on the base table. The new user only has "public" access to the DB.

I've done the samething on other DB and it worked fine, don't know what went wrong on this one?

Any advice?

thanks guys.

Rocky

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/22/2013 :  08:00:56  Show Profile  Reply with Quote
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).
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 02/22/2013 :  09:24:10  Show Profile  Reply with Quote
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).



Thanks James. The view and the base table are in the same DB. How do I check if the view and the base table belongs to different owner?

thanks.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/22/2013 :  10:01:23  Show Profile  Reply with Quote
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.
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 02/22/2013 :  10:21:47  Show Profile  Reply with Quote
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.



Thanks.Just tried and both of them belong to dbo.
I tried following:
select * from myView -- no row returned
select * from myBaseTable -- select denied

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/22/2013 :  10:43:01  Show Profile  Reply with Quote
I am not sure why you are seeing what you are seeing. Even if there was a deny on the table, ownership chaining should have circumvented that. Regardless, can you do two things?

1. Right click on the table name in SSMS object explorer and in properties/permissions see if there are any deny's in the permissions tab.

2. Do an experiment with a test table and test view like shown below:
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;
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 02/22/2013 :  10:58:46  Show Profile  Reply with Quote
I assume "tempTable" should be TestTable?

I got the below message:

The SELECT permission was denied on the object 'TestView'

but select * from TestTable is working fine because the permission was granted.

Edited by - angelran on 02/22/2013 11:00:00
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/22/2013 :  11:04:25  Show Profile  Reply with Quote
Darn,I messed up the whole thing - Was multi tasking at work :) Let me rewrite it again. What I have below is really what I meant, sorry about the confusion:
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;
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 02/22/2013 :  11:17:54  Show Profile  Reply with Quote
Not a problem. :)
I've tried as per your instuction and it worked!!

So I guess something must be messed up in the base table or view?

thanks.
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 02/22/2013 :  11:38:58  Show Profile  Reply with Quote
Ah ha!! It wasn't anything about the ownership chain...I used "IDENT_Current" in the view...of cause it would not run, from my understanding the user needs to be db_onwer to read meta data?

Anyway, thanks heaps for your help James!!

Edited by - angelran on 02/22/2013 11:50:25
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/22/2013 :  11:50:49  Show Profile  Reply with Quote
Not sure that a user needs to be dbowner to view metadata. That would be way too much permission. From this page it seems like the user needs some permission (such as select) on the object http://msdn.microsoft.com/en-us/library/ms175098.aspx

That would explain why you were able to view the data via the view when you granted select on the table.

How to get around that... I can't think of a good option at the moment. The only thing that comes to mind is to create a stored procedure and change the execution context in the stored procedure (and revert after the select statement). But then that wouldn't be the same as a view i.e., you wouldn't be able to use JOINs against it etc.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000