Application Roles For Cross-Database Joins
Aug 25, 2005
I have an application that segregates data into two different
databases. Database A has stored procs that perform joins between
tables in database A and database B. I am thinking that I have reached
the limits of Application Roles, but correct me if I am wrong.
My application creates a connection to database A as 'testuser' with
read only access, then executes sp_setapprole to gain read write
permissions. Even then the only way 'testuser' can get data out of the
databases is via stored procs or views, no access to tables directly.
Anyone know of a solution? Here is the error I get:
Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line
38
Server user 'testuser' is not a valid user in database 'DatabaseB'
The system user is in fact in database A and B.
thanks
Jason Schaitel
View 4 Replies
ADVERTISEMENT
Mar 5, 2006
Hello,
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Happy coding...
View 4 Replies
View Related
Aug 24, 2006
After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
View 14 Replies
View Related
May 28, 2003
We maintain a few applications that query multiple databases on our server. We also have groups of users that multitask using different applications.
I've always created a Role in each database for every application.
Wondering if someone is in two Roles at once will we have security conflicts?
View 4 Replies
View Related
Sep 29, 2015
Will the order of inner joins and cross apply effect the results of a query?
Example:
FROM dbo.vw_Info v
CROSS APPLY dbo.usf_LastFee(v.StoreID, v.AgreementID, v.CustomerID, ABS(v.PrePaymentBalance), cp.ConfirmationNo) lf
INNER JOIN dbo.Customers c
[Code] ....
I want to change the position of doing "CROSS APPLY". Will it effects query results?
View 2 Replies
View Related
Jun 17, 2007
full outer joins and cross joins not working!?!?
am using vc++2005, ADO, and MSAccess 2003. MS "documentation" straight out of the VC++2005 help facility at
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_vdt01/html/419ef633-5a89-41a2-aefe-03540afc9112.htm
provided the following code samples for different types of joins
inner join
Code:
SELECT title, pub_name
FROM titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id left join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles LEFT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idright join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idfull join
Code:SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles FULL OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_idjoin
Code:
SELECT *
FROM authors CROSS JOIN publishers i created two MSAccess Tables:
Merge1:
K1 x
---- ----
a 1
b 2
c 3
Merge2:
K1 x
---- ----
b 20
c 30
d 40
e 50
and executed the following code to test the different joins. the first three joins worked but the last two did not. would appreciate any insight. DBM is an instance of an AccessDBManager class i have written to encapsulate interactions with Access DBs.
Code:
DBM.SQLExtractString = "select * from Merge1 INNER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 2 records returned (K1 = b,c)
Code:DBM.SQLExtractString = "select * from Merge1 LEFT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 3 records returned (K1 = a,b,c)
Code:DBM.SQLExtractString = "select * from Merge1 RIGHT OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this worked - 4 records returned (K1 = b,c,d,e)
Code:DBM.SQLExtractString = "select * from Merge1 FULL OUTER JOIN Merge2 on Merge1.K1 = Merge2.K1";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 5 (K1 should = a,b,c,d,e)
Code:DBM.SQLExtractString = "select * from Merge1 CROSS JOIN Merge2";
DBM.SQLExtract();
s.Format(_T("%d"),DBM.SQLExtractRecords);
MessageBox(s,_T(""),MB_OK);this did not work - 0 records returned instead of 20 (5 * 4)
appreciate any ideas/comments to get the last two joins to work.
thanks - j
View 3 Replies
View Related
Nov 1, 2004
Hi there
Can anyone point me to a good tutorial or give me a run down on using Sql Application roles from my asp.net application. Books Online only give a run down on how to set it up - not how to implement or use it from my code. MSDN no help. Google - same thing.
Any help greatly appreciated.
Thanks in advance
View 2 Replies
View Related
Mar 20, 2008
We have a distributed app that creates it's own instance of sqlexpress when installing. This prevents anyone with sa rights on another instance from accessing our data directly (HIPAA compliance concerns). We are currently looking into making our app easier to install, and want to be able to attach our database to existing instance (if one exists), but still prevent the sa account of that instance from directly accessing/viewing our data. Are application roles the way to accomplish this, is there another way, or is this even possible?
Thanks in advance.
View 5 Replies
View Related
Sep 20, 2011
I have some confusion on crossjoin function within MDx.while I try to crossjoin the different level sets of same Hierarchy. It shows error as
For example.
‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’
select {
{[Customer].[Customer Geography].[Country].&[United States]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?Coz I really need to implement as above MDx within my real Cube.I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.with
member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]
select {
{[Customer].[Country].[United States ]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
View 11 Replies
View Related
Jul 17, 2000
We are looking to make our applications as secure as possible. I am interested in how well Application Roles work to make security tighter.
Have you used Application Roles. If you have, I'd like to know if it helped provide better security or not and if it did, how was it implmented in you production environment. I already know how to get it set up, I'm just wondering if it's really worth the trouble.
Thanks!
View 2 Replies
View Related
May 22, 2003
I may need to setup an application role(s) fro a SQL 2000 db that is being front-ended by VB6. There are 2 types of functions needed, and admin role with access to all tables, and a user role with access to only specific tables. I know from just a straight db roles, I could set up 2 roles, set their rights, and then add the users to these roles. My questions is if I need the same functionality for an Application role, do I need 2 Application roles, one for the admin, and the other for the user?
View 1 Replies
View Related
Jun 13, 2006
We have an an application that was written using OLE DB (ADO) against a SQL 2000 Server that uses an Application role to give rights to the database objects. It connects, calls sp_setapprole and goes on. If the database needs to LOCK a record, it is creating a new ADO Connection and instantiating the Approle again. This model has been working fine up til now.
Now we are installing a SQL 2005 server for the latest version of the product we are working on and are running into an error. The error is
Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
It's happening when the second ADO Connection for locking a record is being created and the sp_setapprole is being executed.
One of my questions is what is the problem with executing the approle on a different connection? Our code has not changed, so obviously SQL 2005 is doing something different. The other is What can we do to correct this?
Is the resource pooling different? We had problems in the beginning with approles and figured out through research that we needed to add OLE DB Services=-2 to the connection string to turn off resource pooling.
Is there an extra step to using Approles in SQL 2005?
Any help would be greatly appreciated as we need to resolve this ASAP.
Thanks,
David
View 5 Replies
View Related
Apr 30, 2007
I want to test Application Roles security for our project, I guess it serves the purpose.
But the quesion I have is if a developer who can look at the application code know's the "Password" can he set the password from Query Analyser and get acess to the database.
Thanks!
View 4 Replies
View Related
Apr 26, 2015
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
View 3 Replies
View Related
Mar 13, 2007
Hello,
in SQL Server 2005 I have an application role that is being used to limit access to my server data from third party applications. Everything is working well, except changing the Application role password.
I set up a small form that allows an administrator to change the App Role password through the front end app. I cannot, however, seem to get the Password field in my approle to accept a parameter.
For example:
declare @newpassword varchar(128)
set @newpassword = 'foo'
ALTER APPLICATION ROLE MyApplicationRole
with PASSWORD = @newpassword
This procedure gives me a syntax error in the last line. It will accept a string in quotes but not a varchar parameter.
Ideas?
View 3 Replies
View Related
Feb 12, 2007
Hi, Am migrating my SQL 2000 legacy app to SQL2005 and am dealing with restrictions on the underlying system tables. Have taken advice that Granting VIEW state to all users is heavy handed (especially meta data access at SERVER level). Now looking at Module signing which is great. I can supply SP's which target the few System table/ information schema fields that I require. Now I Sign the Sp's cool, now I grant exec rights to the application role (doesn't work). Create a db role and put my users in it, okay grant role exec on Sp's (fine they work).
However my application runs under an application role always, so my users rights are ignored and it appears that its only the users not the approles who can benefit from the module signing ? I know I can switch too and from approle using cookies but I seem to be going round in cirlces here.
Essentially is there any 'EASY' and 'CONTROLLED' way that my application user who has no rights, who immediately switches to the application role can see the dbName (All rows ) from master.sysdatabases ?
Thansk for any advice
View 7 Replies
View Related
Nov 24, 2007
Hi all:
[Posting this in the security forum because in this forum I found a related post.]
I have a problem with SQL Server 2005 and application roles and pooling. I needed to use application roles and I needed to use pooling at the same time for an application. I am using sp_setapprole and sp_unsetapprole. In order to ensure that the application role is always set and unset by the application, I actually developed a custom Data Provider based on the SqlClient Data Provider. I have a custom Connection and Command class that wrap the SqlClient versions. Upon opening my custom Connection class, I execute the sp_setapprole stored procedure. Upon closing or disposing the connection, I call sp_unsetapprole.
This works fine in 99% of my tests. However, I have three or four methods (always the same ones, but one only fails ever so often) that fail, but only under the following circumstances:
Pooling is turned on (but pool size doesn't matter)
I am using my custom Data Provider (using System.Data.SqlClient does not cause this issue... but I am also not using approles then)
When other tests have run in the same test run. I.e. when I run the failing methods by themselves in a test run, there is no problem.
So it seems to me that the problem is related to using application roles with pooling turned on. For scalability reasons, we cannot turn pooling off. When the methods fail, I see the following two (2) entries in the SQL Log:
Error: 18059, Severity: 20, State 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
I understand the error message somewhat. However, I am not sure why the "reset" of the connection occurs. My code does not call reset anywhere, so it must be something that happens in the background.
I am reviewing code to see if there is possibly a situation where the sp_unsetapprole procedure does not get called or does not get called successfully, but there is a lot of code (in many custom components).
I would like to know if anyone has a suggestion on how to solve this problem, or, find the code that may be causing the problem.
Thanks in advance,
SA.
View 3 Replies
View Related
Jun 18, 2007
Coldfusion Web appls from Oracle to SQL Server 2005 - How to use Application Roles in Coldfusion.
Is there anyone who has used application roles in Coldfusion Applications? How would you set this up?
I know how to set up application roles. If you establish your application role and you move from one page to another, how would you run cfquery since you loose your initial user connection in place of the application role connection. Are there alternatives to using application roles in Coldfusion?
Sample code would be helpful.
TF
View 4 Replies
View Related
Dec 3, 1999
What are the differences between the database permissions, which can be granted in the database properties permissions tab (create table, create procedure etc.) and the predefined role db_ddladmin? It seems that the database properties permissions tab includes more permissions than the predefined role db_ddladmin.
Does anybody know the difference in terms of permission?
View 1 Replies
View Related
Jan 28, 2004
Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?
I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.
After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?
View 3 Replies
View Related
Jun 16, 2006
Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
Many thanks,
Mark
View 1 Replies
View Related
May 6, 2007
I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
View 3 Replies
View Related
Feb 12, 2008
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
View 2 Replies
View Related
Feb 8, 2007
I am not sure if this is the right place for this question or not but here it goes...
I want to add a new user to the database with read-only rights. I know public is selected by default and can not be changed. I also added the user to db_datareader and db_denydatawriter. But just as I was about to save I noticed Read Only. I have done some searching but have not found anything about this role. I assume it does just what it says but I am confused as to why it would be there when I can set the other settings I just did. Is there any benefit to using this role instead of the ones I chose? Should I add this role in addition to the two I have already picked? Any help is greatly appreciated.
thank you,
Kevin
View 3 Replies
View Related
Feb 19, 2008
Hi All
I have the following questions regrading T-SQL
1. How to assign database role "db_owner" to model database using T-SQL?
2. How to grant a window login public access to master database
Thanks.
View 3 Replies
View Related
Nov 12, 2001
On 11/10/01 I posted a question on how to move passwords from one sql2000 box to another. The response to this question worked perfectly.
I also need to copy over the user or database access information from one box to another. I tried to use dts but it only copied over the users and the database roles but did not copy over the Database user properties which allows the user access and places the user into security groups.
Any help would be appreciated.
Thanks again.
Steve
View 1 Replies
View Related
Jun 26, 2001
After "copying" a SQL Srv DB from one server to the next, we have found that the permissions within the developer-created database roles did not transfer. How can we insert these permissions without doing it manually? Or can we create some type of DTS package that will bring the roles' permissions for us? Or how do you move a SQL database from one server to the next and get everything within it to come along??
View 3 Replies
View Related
Mar 5, 2000
I have been doing maintenance on my sql server 7 databases, I have removed some users from the server login area for example user "EMS". I noticed that "EMS" is still in the 'public database role' for my database. How do I remove old unwanted members out the public database role? Are they perminantly stored there? Is there a way to clean them up?
Does anyone know why users would notbe listed while adding them to roles?
View 1 Replies
View Related
May 4, 2007
I'm looking for advice/caveats about how to convert/export information on AS database / cube roles. The reason why is because we have to move the server into different windows domain and all the roles reference windows accounts in the old domain. Using the MS SQL 2000 version.
Thanks in advance.
-Mike:confused:
View 1 Replies
View Related
May 23, 2005
" I have two sql server2000 database named db1 and db2.
i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1.
now this table was droped and created in db2.
what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.
i have created a view in db1 with the same name as
'create view tableOne as select * from db2..tableOne'
now i can access.
Is there some othere way with out creating view?
View 7 Replies
View Related
May 3, 2005
Hi! Can anyone say which ms sql server predefined roles are similar to the following oracle predefined roles: dba, connect, resource. I already know that sysadmin in MS SQL Server is the same as DBA in Oracle but what about the rest?
Thanks a lot.
View 4 Replies
View Related
Mar 2, 2006
I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality.
I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?
Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database.
Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).
Thank you,
nu_dba
View 1 Replies
View Related
Oct 22, 2007
Hi,
I'm doing a web application that will get some information from an ERP.
At this moment I have 2 databases:
1) The aspnetdb, where I have the tables for Merbership and Role
2) The ERP database
I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.
I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?
Do you think this will work? Can someone make some comments about this situation, and give me some tips?
Thank you!
View 6 Replies
View Related
