You have to understand the problem before proceeding with the solution.
When you select from a view the SQL Server system would check permissions twice:
Once when you select from the view, and once when the view makes reference to the underlying table.
Obviously the second check would fail if the user has no permission on the underlying table.
Microsoft have solved this with "Ownership Chaining" (known as OC).
OC will bypass the permission check that would be done when the view is referencing the table,
Only if the owner of the view is the same as the owner of the table.
The thing is, OC is bypassing permission checks completely, which means that it can bypass denies as well...
For example, if "JoeBlogs" has the create view
permission, he can create a new view which has access to the entire table.
I suggest you read about ownership chaining before you decide your course of action.
books online
msdn blog
detailed example, using a stored procedure instead of view
Having said that, the first Intuitive solution would be to create the view under the same non-dbo schema as the underlying tables (or a schema owned by the same user as the non-dbo schema).
However, if you find that solution to be too risky there is another (and maybe even better) option:
You can always use a function (Multistatement Table-valued Function) with an EXECUTE AS
clause:
Create function syntax
execute as clause
This method will allow you to select from the function (execute
permission on the function) while the function belongs to dbo
. The user specified in the execute as
clause must have permissions on the underlying tables.
It all depends on the definitions and the key (and non-key) columns defined in the nonclustered index. The clustered index is the actual table data. Therefore it contains all of the data in the data pages, whereas the nonclustered index is only containing columns' data as defined in the index creation DDL.
Let's set up a test scenario:
use testdb;
go
if exists (select 1 from sys.tables where name = 'TestTable')
begin
drop table TestTable;
end
create table dbo.TestTable
(
id int identity(1, 1) not null
constraint PK_TestTable_Id primary key clustered,
some_int int not null,
some_string char(128) not null,
some_bigint bigint not null
);
go
create unique index IX_TestTable_SomeInt
on dbo.TestTable(some_int);
go
declare @i int;
set @i = 0;
while @i < 1000
begin
insert into dbo.TestTable(some_int, some_string, some_bigint)
values(@i, 'hello', @i * 1000);
set @i = @i + 1;
end
So we've got a table loaded with 1000 rows, and a clustered index (PK_TestTable_Id
) and a nonclustered index (IX_TestTable_SomeInt
). As you've seen in your testing, but just for thoroughness:
set statistics io on;
set statistics time on;
select some_int
from dbo.TestTable -- with(index(PK_TestTable_Id));
set statistics io off;
set statistics time off;
-- nonclustered index scan (IX_TestTable_SomeInt)
-- logical reads: 4
Here we have a nonclustered index scan on the IX_TestTable_SomeInt
index. We have 4 logical reads for this operation. Now let's force the clustered index to be used.
set statistics io on;
set statistics time on;
select some_int
from dbo.TestTable with(index(PK_TestTable_Id));
set statistics io off;
set statistics time off;
-- clustered index scan (PK_TestTable_Id)
-- logical reads: 22
Here with the clustered index scan we have 22 logical reads. Why? Here's why. It all matters on how many pages that SQL Server has to read in order to grab the entire result set. Get the average row count per page:
select
object_name(i.object_id) as object_name,
i.name as index_name,
i.type_desc,
ips.page_count,
ips.record_count,
ips.record_count / ips.page_count as avg_rows_per_page
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TestTable'), null, null, 'detailed') ips
inner join sys.indexes i
on ips.object_id = i.object_id
and ips.index_id = i.index_id
where ips.index_level = 0;
Take a look at my result set of the above query:
As we can see here, there are an average of 50 rows per page on the leaf pages for the clustered index, and an average of 500 rows per page on the leaf pages for the nonclustered index. Therefore, in order to satisfy the query more pages need to be read from the clustered index.
Best Answer
When a view is created it is bound to the metadata it needs in order to execute.
Note: https://msdn.microsoft.com/en-us/library/ms187821.aspx
That says that
sp_refreshview
: "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends."The outdated metadata binding causes the surprising "wrong" results. Using schema-bound views is one way of avoiding the problem.
Read the link that Aaron Bertrand posted: