table in one dbase,sp in other db on same server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

table in one dbase,sp in other db on same server

Author

Topic

girishhande
Starting Member

26 Posts

Posted - 2009-07-08 : 05:02:44

hi i have one database called dbadmin which contains stored proc sp_getinfo which access table from other dbase db_demo,plz help me hw to implement this scenario?

gkh

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-08 : 05:14:48

Just use three part naming conventions in your select.

select <fields>
from <databasename>.<owner>.<tablename>

girishhande
Starting Member

26 Posts

Posted - 2009-07-08 : 05:19:42

@rickd...
thnks,but its not working,it gives error regarding user cant access something like that...may be becos 2 databases have different users

gkh

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-08 : 05:42:39

You either need to EXECUTE AS (look it up in BOL) the other user, or you need to give the user of the admin db rights to view the table in the other database.

girishhande
Starting Member

26 Posts

Posted - 2009-07-08 : 05:56:08

when did u use cross database ownership?...isthis the case to implement cross database ownership?

gkh

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-08 : 06:41:19

No, you do not need to use cross database ownership, you just need read access to the second databases table or you nmeed to execute the procedure as a user with rights to the second database.

girishhande
Starting Member

26 Posts

Posted - 2009-07-08 : 06:55:03

suppose i have db1 with stored proc sp_insert,and user is udb1
db2 with table empinfo and user udb2...
can u give whole query to insert data plz...i m still geting errors

gkh

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-08 : 07:30:59

Something like this:


use db1
go
create procedure dbo.sp_insert
with execute as 'udb2'
insert into db2.owner.empinfo (<fieldlist>)
values (<your values>)

girishhande
Starting Member

26 Posts

Posted - 2009-07-08 : 07:38:35

its giving error as -
Incorrect syntax near the keyword 'execute'.

gkh

girishhande
Starting Member

26 Posts

Posted - 2009-07-08 : 08:51:02

its giving error as -
Incorrect syntax near the keyword 'execute'.

gkh

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-08 : 10:22:56

What version of SQL Server are you using?

girishhande
Starting Member

26 Posts

Posted - 2009-07-08 : 10:42:59

server 2000

gkh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 11:00:39

quote:
Originally posted by girishhande

hi i have one database called dbadmin which contains stored proc sp_getinfo which access table from other dbase db_demo,plz help me hw to implement this scenario?

gkh

are both dvbs on same server?
Also are both tables owned by same?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-08 : 11:51:12

quote:
Originally posted by girishhande

server 2000

gkh

Sorry, I assumed you were on SQL 2005 as you didn't say. You would need to give yourself access to the table (read access is fine), then you can just run it with 3 part naming convention.

girishhande
Starting Member

26 Posts

Posted - 2009-07-09 : 01:38:04

how to give access to table in other database with different user...and also my databases are hosted on server of hosting company..

gkh

girishhande
Starting Member

26 Posts

Posted - 2009-07-09 : 01:56:22

quote:
Originally posted by visakh16

quote:
Originally posted by girishhande

hi i have one database called dbadmin which contains stored proc sp_getinfo which access table from other dbase db_demo,plz help me hw to implement this scenario?

gkh

are both dvbs on same server?
Also are both tables owned by same?

my databases are on same server and owned by diff users

gkh

girishhande
Starting Member

26 Posts

Posted - 2009-07-09 : 01:56:53

quote:
Originally posted by RickD

quote:
Originally posted by girishhande

server 2000

gkh

Sorry, I assumed you were on SQL 2005 as you didn't say. You would need to give yourself access to the table (read access is fine), then you can just run it with 3 part naming convention.

how to give access to table in other database with different user...and also my databases are hosted on server of hosting company..

gkh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 09:57:24

quote:
Originally posted by girishhande

quote:
Originally posted by visakh16

quote:
Originally posted by girishhande

hi i have one database called dbadmin which contains stored proc sp_getinfo which access table from other dbase db_demo,plz help me hw to implement this scenario?

gkh

are both dvbs on same server?
Also are both tables owned by same?
my databases are on same server and owned by diff users

gkh

then you need to give required permissions on tables to users who execute procedures.

girishhande
Starting Member

26 Posts

Posted - 2009-07-09 : 10:12:23

quote:
Originally posted by visakh16

quote:
Originally posted by girishhande

quote:
Originally posted by visakh16

quote:
Originally posted by girishhande

hi i have one database called dbadmin which contains stored proc sp_getinfo which access table from other dbase db_demo,plz help me hw to implement this scenario?

gkh

are both dvbs on same server?
Also are both tables owned by same?
my databases are on same server and owned by diff users

gkh

then you need to give required permissions on tables to users who execute procedures.

can u explain the whole scenarion in short plz..

gkh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:15:32

i can...but before that you explain what your scenario is with table names and also some description of what procedure does.

girishhande
Starting Member

26 Posts

Posted - 2009-07-09 : 10:26:38

quote:
Originally posted by visakh16

i can...but before that you explain what your scenario is with table names and also some description of what procedure does.

I have one admin db called db_admin and many client dbs as db_cli1,db_cli2,what i want to implement is all stored procedures will contain on db_admin and they will be operating on tables of db_cli1 or db_cli2.All databases are stored on same server of hosting company with sqlserver2000,and each database is having diff user,i have tried adhoc queries but may be thay have disables that option i guess,and link server is of no use in this case as we have only one server.so wht shld i do?

gkh

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources