.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

application role how query a view whose reference table is in another db?

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi everybody. I created  an application role in a database (DB1) and gave it all the rights on a view  in DB1 which refers to a table located in another db (DB2).  I also gave the rights to the app role on a table of  DB1 I tried to use this app. role through the sp_setapprole launched by a user  (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:   The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated.   Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania

View Complete Post

More Related Resource Links

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

Is there a work-around to create indexes for a view defined by a sub-query, derived table, or CTE?


I have three versions of an indexed view I'd like to create. I can create the view, but cannot create a clustered index on (CollectionID, ElementID, TimeID) because the definitions use variously a sub-query, CTE, or derived table, which SQL will not allow.

Has anyone figured a workaround for this? Any suggestions much appreciated.



VIEW [dbo].[aonCompositeFactView] WITH

Problem making Role based Menu in MVC application



 I want to make Rolebase menu in MVC such that if user doesnot have permission for some action then that Action name shouldnot be shown in the Menu.

 I have used the code in the url(http://forums.asp.net/t/1566328.aspx) in my MVC application.My application is a Discussion Forum(in MVC) same functionality as in this forum forums.asp.net

 I have used Controllers for post, thread etc.In each controller there are some actions that are using [Authorize(Roles)] attribute

 but this coding does not count  those Actions in Controllers having Authorize attribute according to the url http://forums.asp.net/t/1566328.aspx 

 In the code, Authorize attribute is applied to Controller class, but my requirement is of Applying Authorize attribute to some actions in controller so that some are available for all users and  some links are available rolewise.Now what is the solution for that?



Security: Unify the Role-Based Security Models for Enterprise and Application Domains with .NET


Role-based security allows administrators to assign access permissions to users based on the roles they play rather than on their individual identities. These privileges can be used to control access to objects and methods, and are easier to identify and maintain than user-based security. The .NET Framework provides two role-based security models, which are exposed as two namespaces: System.Enterprise-Services and System.Security.Permissions. Presented here is a comparison of the two options and a discussion of when each is the right choice. The author also demonstrates the process involved in setting up access security and discusses role memberships.

Juval Lowy

MSDN Magazine May 2002

lookup table query


we are doing our cms migration. both cms's have their own articleID/contentID, we have a lookup table that contain the article URL and its content id.  Now we want to query that table on every single click to get its respective id. 

How and where i can implement this?

Change grid view from table to other HTML layout



You'll have to excuse my ignorance, I'm coming at this from a front-end perspective, and don't really know how to deal with data views in ASP.NET.

Basically, I'm trying to change the layout of some repeated data from a straight table, to something more design-heavy.  It's the same data, just re-organised slightly.  The current ASP.NET code looks like this:

                    <asp:GridView ID="GVMoments" runat="server" AllowPaging="True" AllowSorting="True"
                        AutoGenerateColumns="False" PageSize="20" RowHeaderColumn="FirstName"
                        SkinID="GroupSkin" DataKeyNames="moment_id" 
                        <PagerSettings Mode="NumericFirstLast" PageButtonCount="20" Position="TopAndBottom" />
                        <RowStyle />
                                <asp:BoundField DataField="moment_id" HeaderText="moment_id" ReadOnly="True" SortExpression="moment_id"
                                    Visible="False" In

LINQ Sorting After Goup w/ Table Reference


Having a little trouble here.  I am taking a table (Articles) and grouping all the results by the key that references another table (ArticleSectionCategories).  I then want to sort these results by the OrderBy field of the referenced table (ArticleSectionCategory.OrderBy).  This is what I am using right now but it is not working.  Any thoughts?


                var GroupedArticles = from A in DC.Articles
                                     where A.PackagesId == packageId
                                     group A by A.ArticleSectionCategoriesId into G
                                     select G.OrderBy(C => C.ArticleSectionCategory.OrderBy);


Entity Data Model and database view returning the same columns as there are in a table


When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?

I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.

var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...

Thanks, Jenda

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh

Why does this query timeout in an application but not in Management Studio?

I have a query that looks something like this:   SELECT TOP 1 * FROM Customers c (NOLOCK) WHERE Key = (mykey) AND NOT EXISTS (SELECT 0 FROM Invoices i (NOLOCK) WHERE i.key = c.key AND i.Date > GETDATE() )   When I run this inside an (ODBC) application, it times out.   When I run it within SSMS, it works fine.  That might make you think its an application problem, but the timeout only occurs for one specific customer ... even in the application, it works fine for all other key values.    Any idea where I can even start looking for a solution to this?  Thanks.

Transact-sql - tricky query to get a list of people from a table where i belong too.

I've no idea how to manage and optimize this query: I've four table. - User (userId, name) - Member (memberId, name, ownerId) The ownerId = user.userId - PresentPeople (activityId, presentId, ...) so presentId = memberId - Activity (activityId, ownerId, startDate...) and ownerId = user.userId So here for an user, I need members created by this user, and for this members I need the list of presentPeople for the activity that theyjoined. But I don't know how to get this list because they are in the list. Not sure if i'm clear there, Big help would be very helpfull :)) thanks

How to create a view in my database from a table in another server/database

Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

How to reference 2 columns in a query

Hi All I have 4 tables First Table Structure Id           AccountNum 1                Meals 2                Overtime 3                Wage SSAmount Id     SiteNbr     Meals            Overtime           Wage 1       145              100                 500                   1000 GGAmount Id     SiteNbr     Meals            Overtime           Wage 1       145              100                

sql server query get a list of a table

Hi guys, I've a hard query to manage ... I need to get for a report the number of required people to an appointment, but this query works but just for the regarding person related to the appointment. SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject ,AP.ActivityPartyID ,AP.PartyID ,case when CRMAF_FilteredAppointment.ActivityID is null then null else CRMAF_FilteredContact.ContactID end as s_contactsvisited -- # of contactsvisited FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID <strong> LEFT JOIN FilteredActivityParty AP ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId) AND AP.participationtypemask = '8' -- regarding</strong> LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON AP.ActivityID = CRMAF_FilteredAppointment.ActivityID AND CRMAF_FilteredAppointment.ownerid = SU.SystemUserID AND CRMAF_FilteredAppointment.stat

T-SQL 2005 table query

Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend