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


ADVERTISEMENT

Activation/Security/Dynamic SQL Question

May 12, 2006

I will have a variety of different types of work that will come into my Service Broker queue and I'll likely have a stored procedure or two for each of the different types of work (ie. move order header, move items, move payment, etc.) What is required to be done in each of these steps may vary by the subsidiary and type of order coming in. My plan is to use exclusively stored procedures but to execute them dynamically using sp_executesql. I think I should use sp_executesql because that way I can have a config file (in xml) that I can store what stored procedures need to be called for which unit of work/order type/subsidiary. If I do this I should be able to easily configure each type of work to be done in a config file and let Service Broker handle the execution dynamically. As long as I keep the parameters the same for each of the stored procedures (I'm thinking maybe 4 or 5 parameters) and passing them to each of the stored procedures, this approach will allow me to dynamically configure Service Broker to do what it is supposed to do. I can pull what needs to be done out of the message that comes in with an XQuery expression on the config file. I know that I will have to configure my user (activation user) to be able to run sp_executesql and the security may be complex (especially since I'm using certificates). I can not use trusted databases. Are there any other considerations I should think about?

Gary

View 1 Replies View Related

Question On Internal Activation Stored Procedure Security Context

Jun 25, 2006



CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + """ + AsmName + """ +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";

command.ExecuteNonQuery();

I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

View 13 Replies View Related

Cross DB Dialog Security Issues. Was: Can't Route To Another LOCAL Broker Instance

May 31, 2007

Hi Remus,



I am experiencing the same problem, and I can't get the easy fix to work. I drop and create the DB's in between tests, so it is not related to having an old certificate in the DB, as in the case of Tilfried.



The situation is as follows:

DB1 owned by login1, has a user for login2; this DB is for the initiator

DB2 owned by login2, has a user for login1; this DB hosts the target

Both DB's have TRUSTWORTHY flag set to ON

Error in sys.transmission_queue: 'Error 916, State 3: The server principal "Login1" is not able to access the database "DB2" under the current security context.



Going on a limp, I decide to add a remote service binding in DB1, binding the user for Login2 to the target service, even though BOL explicitly states that this is only required for cross-server communications. This does change the situation - I still get an error, but a new message is sys.transmission_queue: "Dialog security is unavailable for this covnersation because there is no certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation." I already know that the first option works, but I wanted to get the simple solution running. As for the second option, I doublechecked and the initiating procedure DOES already specify ENCRYPTION = OFF in the BEGIN DIALOG CONVERSATION command. My theory is that the remote service binding somehow forces SB to use encryption, but (a) that is not stated in the error message, and (b) if so, then how to get the messages sent over to the target service without using the binding?



==> EDIT: Just saw that you confirmed this theory in your last reply to Tlifried. So I am indeed back to having to find out how to get this to work without remote service binding - it should be possible, but how???



BTW, SELECT @@VERSION shows that I'm on build 3054, in case it matters.



Between all the errors in BOL and less than helpfull error messages produced by SB, I feel like I'm slowly losing my sanity. Please help!



Best regards,



Hugo Kornelis

View 6 Replies View Related

Database Mail And Activation Error

Jul 20, 2007

Hi All,

I'm having some issues getting Database Mail to work with SQL Agent Job's which is doing my fruit !



I'm configuring the server using the following script:




DECLARE


@DataFile varchar(1000),

@LogFile varchar(1000),

@BackupFile varchar(1000),

@cmd Varchar(8000),

@ServerName varchar(1000),

@AccountName Varchar(1000),

@EmailAddress Varchar(1000),

@MailSubject VARCHAR(500),

@FullPath varchar(400),

@Drop varchar(100)

--Configure SQL Server DBMail

SET


@AccountName = (SELECT REPLACE(@@ServerName, '','-') + ' Email Profile')

SET

@EmailAddress = (SELECT REPLACE(@@ServerName, '','-') + '@OurDomain.com')

SET

@ServerName = (SELECT REPLACE(@@ServerName, '','-'))

IF EXISTS

(SELECT Name FROM msdb.dbo.sysmail_profile WHERE NAME = @AccountName)

BEGIN

PRINT 'Database Mail Profile Already Exists - Skiping Step'

END

ELSE

BEGIN

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = @AccountName,

@description = 'Mail account for administrative e-mail.',

@email_address = @EmailAddress,

@replyto_address = 'SQL_Notify@OurDomain.com',

@display_name = @ServerName,

@mailserver_name = 'MailServer.net' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = @AccountName,

@description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @AccountName,

@account_name = @AccountName,

@sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = @AccountName,

@principal_name = 'Public',

@is_default = 1 ;

EXEC

msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @AccountName

SET

@MailSubject = 'Database Mail Test Message from ' + @@SERVERNAME

PRINT

'Created DBMail Profile'

END

GO

DECLARE @MailSubject VARCHAR(500)

DECLARE @AccountName Varchar(1000)

SET

@AccountName = (SELECT REPLACE(@@ServerName, '','-') + ' Email Profile')

EXEC

msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default Email Profile'

SET

@MailSubject = 'Database Mail Test Message from ' + @@SERVERNAME

--Check if DB Operator alredy exists; Create if not.

IF EXISTS (Select Name FROM MSDB.dbo.SysOperators WHERE NAME = 'SQL Notify')

BEGIN

PRINT 'Database Operator Already Exists - Skipping Step'

END

ELSE

BEGIN

EXEC msdb.dbo.sp_add_operator @name=N'SQL Notify',@enabled=1, @pager_days=0, @email_address=N'SQL_Notify@OurDomain.com'

END

EXEC

msdb.dbo.sp_send_dbmail

@profile_name = @AccountName,

@recipients = 'SQL_Notify@OurDomain.com',

@subject = @MailSubject,

@body = 'This is a Database Mail Test Message'

GO



OK - I get the test message, however when I setup a SQL Agent job later in the same script to alert me on failure; I dont get any mails through.



When I check the SysMail Event Log; I see the followign error:



error 2007-07-20 15:58:07.623 Activation failure. NULL NULL NULL 2007-07-20 15:58:07.623 sa



The SQLAgent is running under the same account as SQLServer; neither are local admin's on the machine. I've restatred SQLAgent using SSCM a number of times yet nothing comes through.



Mail Profile is started; Service Broker is enabled in MSDB



SQL Agent is configured with the correct profile; an operator is set as a failsafe operator.



Does anyone have any suggestions?????

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 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 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 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 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

Confusing Cross-Database Permissions Issue

Jul 25, 2007

We're trying to follow the principle of least privilege here in setting up a user account for our website to use to access SQL Server 2005, but we're having a nightmarish time getting it to work.

The issue seems to be trying to get a limited access user account the ability to cross databases.

Here's the situation:

We have a User [WebUser] that we want to grant access to the database. This account has a login [WebUser] that has username=WebUser and password=ALongPassword.

This user only calls stored procedures in the database [WebData].

However, some of the stored procedures in [WebData] call stored procedures in the database [dbutil].

One of the stored procedures in [dbutil] inserts records into a table in a third database [dbutil_temp].[DebugLog].

This all works out great from my development account using Windows Authentication.

But as you might guess, if I do something like "EXECUTE AS [WebUser]" and run the same procedure on [WebData] things fall apart quickly. I've looked online regarding cross-database ownership chaining, but quite frankly, the whole users/logins/roles/schemas security model is confusing, and I'm getting nowhere fast on my own.

We really only want [WebUser] to have CONNECT and EXECUTE permissions on the primary [WebData] database, but it seems like we've got to do a lot more than that to get this to work.

I'd appreciate any help...

View 5 Replies View Related

Transact SQL :: Bug In REVERT During Cross Database Call

Jul 28, 2015

I have noticed rather strange behaviour of EXECUTE AS and REVERT sequence during the cross database calls which appear to be a bug. I tested this issue on developer edition of SQL Server 2012

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
 May  4 2015 19:11:32
 Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

This issue causes problems in SSISDB where similar piece of code appears in [catalog].[start_execution] and some other scripts in the [internal] schema. This was previous discussed in [URL] The following script illustrates the issue:

USE [master]
GO
CREATE DATABASE [Test1]
GO
CREATE DATABASE [Test2]
GO

-- Set Database to Trustworthy to allow cross database connection

ALTER DATABASE [Test1] SET TRUSTWORTHY ON;
GO
ALTER DATABASE [Test2] SET TRUSTWORTHY ON;
GO
USE [Test2]
GO
CREATE PROCEDURE [dbo].[TestContext]
AS
SELECT 'EXECUTION CONTEXT BEFORE EXECUTE AS CALLER', SUSER_NAME(), USER_NAME();

[code]....

View 8 Replies View Related

User Define Roles And Cross Database Queries

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

How To Use Triggers To Enforce Cross-database Referential Integrity?

Aug 1, 2001

Can someone give me an example on how to enforce cross-database referential integrity with triggers in SQL Server 2000?

Thanks,
Joel

View 1 Replies View Related







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