How to: Select from schema1.view in dbo.mystoredproc by specifying only select * from view - Wally Highsmith

28-Nov-07 11:29:22
I have...
a table called dbo.MYTABLE
a view called schema1.MYTABLE (that selects from dbo.MYTABLE)
a stored procedure called dbo.proc_MYTABLESELECT that has (SELECT * FROM
MYTABLE - Note no schema specified on table name)
a login\user with default schema = schema1

The login\user also has select privileges on the dbo schema.
The login\user has execute privileges on dbo.proc_MYTABLESELECT

When the logged-in user executes SELECT * FROM MYTABLE, he is selecting from
the view because his default schema is schema1.
But when the logged-in user executes "exec proc_MYTABLESELECT", he is forced
to select from the table
in the dbo schema instead of the view in schema1.

Is there anyway to force dbo.proc_MYTABLESELECT to select from the MYTABLE
view in schema1 instead of the actual table in dbo without changing the
stored procedure?
reply
 
 

How to: Select from schema1.view in dbo.mystoredproc by specifying only select * from view - Uri Dimant

29-Nov-07 01:54:13
Please read this great article
http://www.sommarskog.se/grantperm.html
reply
 
Min Question
promotion
Silverlight    WPF    WCF    WWF    LINQ   
JavaScript    AJAX    ASP.NET    XAML   
C#    VB.NET    VB 6.0    GDI+    IIS    XML   
.NET Generics    Anonymous Methods    Delegate   
Visual Studio .NET    Expression Blend    Virus   
Windows Vista    Windows XP    Windows Update   
Windows 2003 Server    Windows 2008 Server   
SQL Server    Microsoft Excel    Microsoft Word   
SharePoint    BizTalk    Virtual Earth   
.NET Compact Framework    Web Service   

"Everything" RSS / ATOM Feed Parser
How to send and receive messages through message queuing in .Net
How to Read text file as database
SQL Server 2005 Paging Performance Tip
Display code of web page.
Fully Scalable Excel File Importer class for .net using Microsoft Jet driver
Generic Chart Color Manager class that can be used for any charts
Helper class to style the infragistics wingrid
Using Reflection to detemine as Assembly Info in and out.
Helper class to play with Window (Owners and position)
Resolving displayname from the culture using the XmlLanguage and LanguageSpecificStringDictionary class