Application Role How Query A View Whose Reference Table Is In Another Db?
Aug 23, 2007
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
Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) for the given connection string Dim connstring As String = "Data Source=Northwind;Initial Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
This one is a real X FIle, just without Mulder, Scully or the Lone Gunmen!
I have a database, to which access must be restricted via a sole application. So, I have to use an application role. I go in the database and run these statements to add and activate the roles, respectively;
Exec sp_addapprole 'Sirius', 'password'
(The system confirms the role is created.)
Exec sp_setapprole 'Sirius', 'password' 'odbc'
(The system confirms the role is activated.)
Right, now I should not be able to connect using anything but this role, agreed? But here's where things go wrong. I can then successfully connect from another computer by using MS query from Excel, from a login that is not even a member of the Public Role!
I tried again, started and stopped the Server/DTS/Agent services and dropped the old role after each successful login before recreating it. I've checked my syntax exhaustively. I must be doing something wrong, or overlooking something, otherwise MS has a major security problem! (Just hope the Cancer Man doesn't find out!!)
I understand that this procedure connects to my sqlserver database as my application role
Ok, so far no problems in reading and manipulating data.
The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....
I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role).
So, my cuestion is: How can I tell Report Server to use the application role to display reports?
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data. I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database?
Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator?
I know how to create an Application role in SQL server 7. Now how do I connect to the database from VB using that Application Role? I can't find anything about this topic anywhere. Is this the purpose of an Application role or am I way off?
I'm testing the use of application roles for security. The customer I work for has still a lot of ASP intranet applications running. We're migrating the databases to a SQL Server 2005 server.
I've changed the connection string to a user without any permissions but to log on. After that I use an application role for permission to select different tables and to execute Stored Procedures.
The first queries do execute but after that I get "Permission denied", like I haven't got the application role anymore.
I want to add bulkadmin permission to my applicatio role. Is it aposible.My windows account havo only public permission on database.I'm using application roleEXEC sp_approlepassword 'MyRole', 'password';Therefore I want to BULK some data with BULK INSERT command.Error is:The current user is not the database or object owner of table'tablename'. Cannot perform SET operation.Thanks in advance.
In SQL Server 2005, you must have the VIEW SERVER STATE permission in order to access sys.syslockinfo (http://msdn2.microsoft.com/en-us/library/ms189497).
Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?
Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005
The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate".
If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails)
sp_setapprole 'SearchAppRole', 'password'
select * from recipe
If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement:
However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error:
System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj
I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.
I am attempting to use dbmail from an application that logs in to my database using an application role. Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it: I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights.
Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail. But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission." When I run my spSendEmail stored procedure from the calling database, I get the same error.
I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005.
My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables: 1) Table A 2) Table B 3) Table C
The USER login is a restricted user which has only SELECT permission to Table A, B and C.
I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime".
Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A.
I have tried for almost 2 days but am still clueless. Any ideas?
Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).
Developing a desktop application which calls a local instance of ".sqlexpress".
This app needs to set data base options and add/del various table columns.
When activating the application role, I get the following message:
HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.
Anyone know what this message means?
I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.
I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this??
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
I am very frustrated. Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard. Please help!
Can anyone recommend a good reference book on views, stored procedures (system stored procedures as well) and triggers? I am in need of assistance in these areas.
My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.
After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.
In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.
In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.
Any advice on how to duplicate the behavior from SQL2000?
guys, ive never worked with Views before so forgive me. i know how to create one, and that it creates a virtual table in memory, but i've got one small question.
if i create a view:
CREATE view dbo.myView as select Distinct FirstName,LastName from SomeTable
When ever i reference that view, such as Select FirstName,LastName from myView where LastName like 'Jo%'
does that View Refresh itself??
in other words does it run each time i Reference it??? or is it static from when i created it.
Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?
I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?
Code is below:-
--create test user CREATE USER TestDenyOnViewUser WITHOUT LOGIN GO
--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply) CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo
Hi there,I struggle to get this going i would like to insert data into 2 tmptables in a view.If i run the code on it's own it works perfectly until i want to createa view it complains about the INSERTthis is my codeCreate view dbo.vew_SwitchesAsINSERT INTO tmpInsSelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.INAME,MB.NOfrom Cars BIVLEFT JOIN MountainBikes MBON MB.ID = BIV.IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'IN'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC------ Step 2 -------Into tmpOutsInsert Into tmpOuts ---- All Switches In ----SelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.NAME,MB.NOfrom Cars BIVLEFT JOIN Mountainbikes MBON MB.ID = BIV._IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'OUT'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC----------------------Step 3 ----------------SelectDistinctins.DATE,ins.ID,ins.NAME ,insIND,ins.AMOUNT/100 as AmountIn,outs.IND,outs.AMOUNT/100 as AmountOut,outs.NAME
I am having the following situation - there is a view that aggregates and computes some values and a table that I need the details from so I join them filtering on the primary key of the table. The execution plan shows that the view is executed without any filtering so it returns 140 000 rows which are later filtered by the join operation a hash table match. This hash table match takes 47% of the query cost. I tried selecting the same view but directly giving a where clause without the join €“ it gave a completely different execution plan. Using the second method is in at least 4 folds faster and is going only through Index Seeks and nested loops. So I tried modifying the query with third version. It gave almost the same execution plan as the version 1 with the join operation. It seams that by giving the where clause directly the execution plan chosen by the query optimizer is completely different €“ it filters the view and the results from it and returns it at the same time, in contrast to the first version where the view is executed and return and later filtered. Is it possible to change the query some how so that it filters the view before been joined to the table.
Any suggestions will be appreciated greatly Stoil Pankov
"vHCItemLimitUsed" - this is the view "tHCContractInsured" - this is the table "ixHCContractInsuredID" - is the primary key of the table
Here is a simple representation of the effect:
Version 1: select * from dbo.vHCItemLimitUsed inner join tHCContractInsured on vHCItemLimitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID where tHCContractInsured.ixHCContractInsuredID in (9012,9013,9014,9015)
Version 2: select * from vHCItemLimitUsed where ixHCContractInsuredID in (9012,9013,9014,9015)
Version 3: select * from dbo.vHCItemLimitUsed where ixHCContractInsuredID in (select ixHCContractInsuredID from tHCContractInsured where ixHCContractInsuredID in (9012,9013,9014,9015))
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error. Select a.email, case when a.reportnumber like '1%' then (select b.Reportnumber from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Reportnumber, case when a.Reportnumber like '1%' then (select b.stonebreakdown from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Measurement, case when a.Reportnumber like '1%' then (select b.reportcarddate from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as ijasDate, case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo from t_RegisterInfoTemp a Query works fine like this but when i add this (the one marked bold i get error) case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo,case when a.reportnumber like '2%' then (select c.Measurement from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as Measurement2
This is the error. Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt. For example: Table Structure: ============ Application: ApplicationID, Name, Address, City, State, Zip, etc.. ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt
View should display: ============== ApplicantID, ApplicantActionID, Status, CreateDt
