Cross Database Ownership Chaining

Mar 10, 2008

i have a database with cross database ownership chaining enabled. data base was detached and reatached as a result owner changed from sa to account that was used during reataching. will this affect chaining?

View 1 Replies


ADVERTISEMENT

Cross Database Ownership Chaining

Oct 5, 2006

I saw this option in SSMS under "Servers, Property, Security tab". I looked at BOL but I still don't understand what it is or what it is used. Any help appreciated.



TIA,



Barkingdog

View 3 Replies View Related

Cross Database Ownership Chaining Not Working With Windows Group

Mar 18, 2008

We are having a problem with cross database ownership chaining. Below is a description of the problem:

I have a domain group named DOM1GROUP1
I have a domain user DOM2USER1 who is a member of DOM1GROUP1 (note that they are in different domains)
I have a database DB1 which contains a stored procedure (st_insertdata) that does an insert in a table (tb_data) on DB2
DOM1GROUP1 has been granted login rights on the SQL Server
DOM1GROUP1 is a user in both DB1 and DB2
DOM1GROUP1 has execute rights on procedure st_insertdata and insert rights on table tb_data.
All objects are owned by the dbo schema.
The database owner for DB1 and DB2 is sa

When DOM1USER1 executes st_insertdata an error is returned:
The server principal "DOM1USER1" is not able to access the database "DB2" under the current security context.

I've played around with the options "trustworthy" and "db chaining" but these do not make any difference. The only thing that fixes this problem is if I create a login for DOM2USER1 and grant it access to DB2 (with no other rights other than membership of the public role).

It seems that SQL Server does not recognize that DOM2USER1 is a user in DB2 by virtue of its membership of the domain group DOM1GROUP1. Is there a way to get this to work without granting explicit rights to DOM2USER1?

View 4 Replies View Related

SQL 2012 :: DB Chaining And Ownership Chains

Apr 22, 2014

I am getting the following error when trying out DB chaining and Ownership chains

Msg 916, Level 14, State 1, Line 1

The server principal "user" is not able to access the database "Test" under the current security context.

I did run these scripts to setup DB Chaining

ALTER DATABASE [Test1] SET DB_CHAINING ON;
ALTER DATABASE [Test] SET DB_CHAINING ON;

I then setup user using a sysadmin role with select permission to a view. The View is on Test1 and points to Test database using a synonymn setup on Test1.

According to all the articles I have read this is all I should do and the view should then run.

View 3 Replies View Related

Ownership Chain Doesn't Work When Access Cross Database

Oct 25, 2007

i have enable cross database chain,but it return error message:



The server principal "S-1-9-3-1149532189-1170944071-2610337685-3868961652." is not able to access the database "db2" under the current security context.


I list the sql script as follows:






Code Block


use master;
go
create database db1;
create database db2;
go
use db2
go
create table table1
(
col int
)
go
use db1
go

create user u1 without login
go
create proc p1
as
insert into db2.dbo.table1 values(1)
go
grant execute on p1 to u1



execute as user='u1'
exec p1





thanks

View 9 Replies View Related

Database Ownership

Apr 1, 2008

My system was crashing so I reinstalled everything. including SQL 2005 Express with management studio. I renamed the computer from the previous install. but for some reason the sql 2005 express management studio remembers the old machine name even when I create a new DB inside the management studio with the new computer name.when I try to change the ownership in properties-files it cant find the 'new computer nameuser'i double checked the computer name its correct. The funny thing is i installed express with the 'new computerame' anyone have a clue that may help with this what file needs to be edited?Thanks inadvance for any help. 

View 2 Replies View Related

Dbo - Database Ownership

Jun 8, 2006

I have created tables within SQL Server database. My tables I have created do not have the dbo ownership(?)...how does one transfer a table to dbo?

advTHANKSance

View 7 Replies View Related

Replicate Database Objects That Are Not Dbo Ownership

Aug 25, 2006

I have a database called sky and its tables, views, procs and functions owned by sky. I need to replicate the sky database to another server. I had problem because those objects have ownership sky not dbo. I can not change ownership when replicate the database. How do I replicate database objects that are not owned by dbo? Is this possible or I have to change ownership from sky to dbo before replicate the database?



Thank you very much for your input and suggestions.

View 1 Replies View Related

Need Tool For Managing Database Object Ownership

Jun 26, 2006

we have a group of developers which have created and asked us (DBAs) tocreate many objects in the databases including tables / storedprocedures / functions / etc.since our company is growing, however we have an increasing amount ofobjects that have either been abandoned or have several versions.in an effort to clean of the huge amount of clutter and anytime thatsomething simple like a stored proc needs to change, it is almostimpossible to predict exactly where we will see negative effects ofthis change.i am looking for a system (preferably without developing our own tool)that would keep track of history of database objects (in terms of whocreated it and what purpose it has) as well as link that to all thedevelopers/users we might need to notify of any changes to that object.Also, this should be linked to the application which rely on theobject.bottom line... every object in the database needs to have at least 1corresponding contact as well as the applications which us it.with this information, we can much more easily maintain objects in ourDBs.thx

View 3 Replies View Related

SQLEXPRESS Database Ownership, Sa, And Authentication Troubles

Dec 25, 2005

I installed VS2005 on my laptop at the office and of course, got the default instance of SQLEXPRESS. When I take my laptop home and log in (I use my NETWORKlogin login everywhere), I can't authenticate to use the database -- it doesn't like my login unless I'm physically at my office.

So, thinking I'm clever, I changed my SQLEXPRESS instance to use 'SQL Server and Windows Authentication mode' and tried to use the 'sa' login in my connection string. Login failed for 'sa' and since I had never set a password (yes, I tried using a blank) I executed the following query on my db:

exec sp_password @new='WHATEVERPASSWORD', @loginame = 'sa'

The query works (?) but it my connection string doesn't and I'm still unable to use the management tool when not on the original network.

So, I thought I'd add a new, super secret special user, but alas, that has completely escaped me. That frightens me a bit because I'd really like to be able to deploy my database to my web host and trust the my connection strings will work.

So, here are my questions:

1. Can I set up my instance so that I don't have to be on the network on which I installed it in order for it to recognize me (since it's self contained on my machine)? If so, how?

2. How do I create new SQL users?

3. Has the connection string changed significantly, and consequently, am I missing something fundamental in my attempts to connect? If someone could toss out a working SQL Server Express 2005 connection string that doesn't use Windows authentication, that would be really appreciated.

Here's some info from my box, if it helps.

Microsoft SQL Server Management Studio Express      9.00.1399.00
Microsoft Data Access Components (MDAC)      2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML      2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer      6.0.2900.2180
Microsoft .NET Framework      2.0.50727.42
Operating System      5.1.2600

Thanks for any help out there.

View 3 Replies View Related

Multiple Database / Cross Database Referencial Integrity (foreign Keys)

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

Cross Database

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

Cross-database References

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

Cross Database Updates?

May 8, 2008

I am interested in adding a new row to a  table  'Table05'  that exists in a SQL Server 2005 database whenever  a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?
 

View 1 Replies View Related

Cross-database Query From ASP.NET

Feb 7, 2005

How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?

View 1 Replies View Related

Cross Database Operations

May 6, 2008

Hi,
I'm trying to get data from several databses of different kinds (Sql Server and Oracle) and from different servers - is that possible ?
If not - is there any way to copy some data from one database to another - like tables and their content (or partial content) ?

I tried to use "select name from <database name>..sysobjects where xtype = 'U'" but I get security error.

thanks,
Naama

View 2 Replies View Related

Cross Database Queries

Apr 27, 2004

i know that db.owner.tablename works in the query analyzer, but what i really appreciate from anyone is how to apply this in vb6 code since the recordset is opened only from one db using the following syntax:

rs.open "select ...", dbname, ..., adopendynamic, adlockoptimistic ...

thanks

View 1 Replies View Related

Cross Database Manipulation

May 4, 2004

Hi everyone!

I recently found out that using ADO to connect to the SQL server without mentionning the data source; then, by sending a query with the following syntax: dbname.owner.tablename.columnname... an implicit connection to the data source (database) is performed automatically. This way of connecting allowed me to manipulate accross two or more databases thing that is necessary in my project.
My question is:

1- Do anyone have any bad experience and/or negative consequence to such connectivity (memory consumption, unexpected disconnection, ...)? Please consider a very high frequency of manipulation since we are dealing here with a 24/24 hour operational site.

2- Are there any alternative solutions for cross database manipulation (select, insert, update, delete)?

Thanks

View 2 Replies View Related

Cross Database Dependencies

Jan 3, 2008

As far as I know SQL Server 2005 lists only dependencies within the same database. In many cases though there are objects in one database depending on objects in a different database on the same server. I unsuccessfully looked for a tool to list those and I ended up creating a script to do the job instead. Any improvements are appreciated, e.g. the script uses syscomments which may no longer be available in the next SQL Server version - I have not found a replacement sys.XXX table yet. Please note that you need to use a connection with access to all the SQL databases on a server and that it may take a while to run the script below if you have many databases with numerous objects in there. On the other hand these are exactly the cases when you need to know which of the thousands of objects are dependent on the table or view you are looking at.

P.S. Does anybody know where the extended properties get stored (hint - not in syscomments)? I was looking for those too, since often I document in the description of a field that it links to "DatabaseABC.dbo.TableXYZ.Field1"

Plamen Kouzov

==========
declare @searchstring varchar(50)
declare @dbid int
declare @DBName varchar(1024)
declare @command varchar(1024)
declare @ServerDBs table([dbid] smallint, DBName varchar(1024))
select @searchstring = 'DatabaseABC.dbo.TableXYZ'
insert into @ServerDBs ([dbid], DBName)
select [dbid], [name]
from master.dbo.sysdatabases
where [name] not in ('master','tempdb','model','msdb','pubs','Northwind')

create table #ObjectsFound (DBName varchar(500), ObjectName varchar(500))
while (select count(*) from @ServerDBs)>0
BEGIN
set @dbid = (select TOP 1 dbid from @ServerDBs)
set @DBName = (select DBName from @ServerDBs where dbid = @dbid)
set @command = 'use ' + @DBName + '; insert into #ObjectsFound(DBName, ObjectName) select ''' + @DBName + ''', [name] from sysobjects where id in (select [id] from syscomments where text like ''%' + @searchstring + '%'') order by 1'
exec sp_sqlexec @command
delete from @ServerDBs where dbid = @dbid
END
select * from #ObjectsFound order by 1,2
drop table #ObjectsFound

View 1 Replies View Related

Cross Geo Database Mirroring

Feb 26, 2008

I'm not personally a DBA or claim to be a database expert, instead I am a software architect who works closely with database architects and DBA's. I'm presently architecting a system where we will have a smart client used in both Europe and the United States from the same database instance.

The system is to be used for performance testing of devices and then the results will be available to management through the same smart client used by test engineers etc. The first release is operational in the United States today with Poland coming online shortly.

Today we are weighing our options between having cross-geo database mirroring or merely having the database server in the United States with application server level caching implemented in Poland and the United States.

The .net framework's Windows Communication Foundation is used with a netTcpBinding and the service layer is asynchronous with the reliableSession option enabled.

Does anyone have any feedback on whether you would go with cross-geo database mirroring or would you use local application servers with possibly some caching capabilities at that layer? Please also describe the benefits and any risks associated with each also...

Thanks in advance,

Doug Holland

View 8 Replies View Related

Cross Database Query

Feb 18, 2008

I have Two databases e.g. PDC,STB(has a table named "user" and fields of the table is "name","time_of_login").i want to create a stored procedure in PDC database which will insert data into STB's "user" table.

OR

how to run query from QueryAnalyzer by setting database from combobox at top as PDC and run select * from STB.user;

AS

in oracle we can connect via one user
and can select the tables of another user.
like
connect internal/oracle
select * from scott.emp;

View 4 Replies View Related

How Can I Create A Cross-database Dependencies?

Nov 4, 2002

I have a table EMPLOYEE in database PERSONNEL, and a table JOBS in database JOBMAN, I want to create a dependencies on the primary key EMP_NUM in table EMPLOYEE and the foreign key EMP_NUM in table JOBS, how can I do it in Enterprise Manager or any other tools?

View 5 Replies View Related

Cross Server Database Dumps

Oct 5, 2004

I am attempting to remember the string to dump/restore a database to a different physical server.

I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?

Thanks

View 3 Replies View Related

Cross Server Database Dump

Oct 5, 2004

I am attempting to remember the string to dump/restore a database to a different physical server.

I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?

Thanks

View 2 Replies View Related

Cross-database Dependency Check

Oct 28, 2004

Is there anyway to check for cross-database dependencies?

For example:
I have a stored proc in the Pubs database that contains a query that references a table in the Northwind database (as well as reference a table in the Pubs database.) I'm having no problems finding the depencency for the Pubs table. I can't seem to find the dependency for the Northwind table.

Any help would be appreciated.

Thanks in advance.

Scott

View 1 Replies View Related

Cross Database Referencial Integrity

Feb 12, 2008

Using SQL Server 2005, I have two databases.

AppDB - The main application database.
GeoDB - A somewhat static ZIP code / states / other geographic stuff database



I 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).

SQLServer2005 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 itwouldn'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, any links to articles, or any more options I am missing.





Thanks,Adam Nofsinger
ucnmedia.com

View 9 Replies View Related

Cross Database Activation Security

May 25, 2006

I have two databases Basket_ODS and Intelligence_ODS.

I created a user in the Basket_ODS and Intelligence_ODS databases as follows:

USE Basket_ods

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'

CREATE USER BasketServiceUser WITHOUT LOGIN

ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser

GRANT CONTROL ON SERVICE::[Order Send]

TO BasketServiceUser

CREATE CERTIFICATE BasketServiceCertPriv

AUTHORIZATION BasketServiceUser

WITH SUBJECT = 'ForBasketService'

BACKUP CERTIFICATE BasketServiceCertPriv

TO FILE = 'BasketServiceCertPub'

In the other database...

I created the following:

USE Intelligence_ODS

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************

USE Intelligence_ODS

GO

CREATE USER BasketServiceUser WITHOUT LOGIN

CREATE CERTIFICATE BasketServiceCertPub

AUTHORIZATION BasketServiceUser

FROM FILE = 'BasketServiceCertPub'

My Queue is in BASKET_ODS and is set up as:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BasketServiceUser'

)

I have performed the following grants in Basket_ODS

grant execute on ODS.ProcessOrderQueue to BasketServiceUser

ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.

grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser

ProcessOrderQueue proc is set as follows:

ALTER procedure [ODS].[ProcessOrderQueue]

WITH EXECUTE AS 'BasketServiceUser'

[ODS].[MoveOrderTotals_Core] is set up as follows:

when I run ProcessOrderQueue I get an error message:

ALTER procedure [ODS].[MoveOrderTotals_Core](@Orderid uniqueidentifier)

with execute as 'BasketServiceUser'

I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)

The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.

Can you help me figure out what I'm doing wrong. I've spent so much time on this security stuff. Is there another way to do this that is more straight forward without using database trust?

View 3 Replies View Related

Mirroring And Cross Database Queries

Feb 4, 2008

This may be a newbie question but...

Say for example DatabaseA has failed over to the mirror. Then you call a stored procedure in that database that queries a table on DatabaseB that is on the principle (has not failed). What happens? Is mirroring smart enough to know that? If not, are there workarounds? Perphaps manually failing over the other databases?

Thanks

View 2 Replies View Related

Workaround For Cross Database Queries

Feb 6, 2007

Hi all,

Could you please suggest workaround for systems that have cross database queries and want to use mirroring.

Thanks,
Avi

View 2 Replies View Related

How Efficient Is Cross Database Selects

May 23, 2008



Hello,

I want to know how efficient are cross database selects such as

select column from data.dbo.table;

The reason I'm inquiring is because I want to design a database whereas I'm tightly coupling the tables in several databases that are all referencing each other in some way shape or form.

If it is not too efficient or if you know of a way to make these cross database selects more efficient please offer the suggestion.

Thank,
Sharp_At_C

View 2 Replies View Related

Cross Database Query Performance

Feb 1, 2008

What is the relative performance cost of peforming joins, inserts across databases as opposed to performing them on tables within the same physical database? I can't find much on this at all..

Thanks

View 12 Replies View Related

Cross Database Query Performance

Feb 17, 2008

Hello All,

I have an issue where my SQL query is taking an exceedingly long time to execute. It joins tables in 2 different databases. When I import the tables from both my databases into a single database and execute the query, it executes instantly!

I had a look at the execution plans of the same query when executed within a single database and the query when executed with tables across databases and there were differences in the execution plan. The single database query had parallelism whereas the query for cross database joins didnt have any parallelism.

Both the databases are in the same server.

I havent seen a case where cross database joins impact query performance so much. Any pointers?

Thanks
Arun

View 4 Replies View Related

Application Roles For Cross-Database Joins

Aug 25, 2005

I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases 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, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved