"Jure Bogataj" <jure.boga***@mikrocop.com> wrote in message
news:eU4veNOxFHA.2228@TK2MSFTNGP11.phx.gbl...
> Hello all!
>
> I'm having problems regarding executing stored procedures under differents
> users or different database. I have, let's say 100 tables with the same
> name, but different owners. Picture this scenario:
>
> User: dbo (login: sa) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[dbo].[MYTEST])
>
> User: u1 (login: u1) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u1].[MYTEST])
> User: u2 (login: u2) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u2].[MYTEST])
> User: u3 (login: u3) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u3].[MYTEST])
> User: u4 (login: u4) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u4].[MYTEST])
> User: u5 (login: u5) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u5].[MYTEST])
> User: u6 (login: u6) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u6].[MYTEST])
> User: u7 (login: u7) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u7].[MYTEST])
> User: u8 (login: u8) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u8].[MYTEST])
> User: u9 (login: u9) Table: MYTEST (FullName:
> [MyServer].[MyDatabase].[u9].[MYTEST])
>
> Now, I want to be able to create stored procedure under dbo (let's say
> dbo.MyStoredProc), which will, when executed read contents of the table
> for
> user which is logged in (e.g. through QueryAnalyzer).
> Basicly this means, that I'm creating procedure:
> create procedure dbo.MyStoredProc
> as
> select * from MYTEST
>
> This does not work:
> create procedure dbo.MyStoredProc
> as
> select * from user_name().MYTEST
>
>
> Now, when I login to QueryAnalyzer as u1, I call this SP as:
> exec dbo.MyStoredProc
>
> This will execute SP and read records from dbo.MYTEST. Am I right? How to
> achieve to read records from u1.MYTEST?
> I could write "select * from u1.MYTEST" in SP, but this would require
> different SP for each distinct user. I want to have only one stored
> procedure under one user, so if I will have to correct it, I will do so in
> one place not for every user that uses this SP. SP is of course much more
> complicated that the one used in this post, but I think it is enough to
> show
> problem... And what if add some more users... The number of SP's will only
> grow.
>
> I need something like: select * from @current_loggedon_user.MYTEST? Is
> this
> possible in SQL Server (or any database, for that matter) at all? I know
> that I can construct dynamic query and run it with sp_executesql, but I
> wonder if there is any other possible solution?
>
> Any help greatly appreciated!
>
>
> regards, Jure
>
>
>
>
>
>
>
>
>
>
>

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:uP4CXROxFHA.1148@TK2MSFTNGP11.phx.gbl...
> Jure
> I think your approach is wrong
>
> Look at this script helps you ( I would not rely on sp_MS_marksystemobject
> in the production)
>
> use master
> create table t(c1 varchar(50)) insert t values('master')
> go
> create proc sp_test as select * from t
> GO
> use northwind
> create table t(c1 varchar(50)) insert t values('northwind')
> use pubs
> create table t(c1 varchar(50)) insert t values('pubs')
> use pubs
> exec sp_test --returns 'master'
> use master
> exec sp_MS_marksystemobject sp_test
> use pubs
> exec sp_test --returns 'pubs'
> use northwind
> exec sp_test --returns 'northwind'
>
>
> "Jure Bogataj" <jure.boga***@mikrocop.com> wrote in message
> news:eU4veNOxFHA.2228@TK2MSFTNGP11.phx.gbl...
> > Hello all!
> >
> > I'm having problems regarding executing stored procedures under
differents
> > users or different database. I have, let's say 100 tables with the same
> > name, but different owners. Picture this scenario:
> >
> > User: dbo (login: sa) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[dbo].[MYTEST])
> >
> > User: u1 (login: u1) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u1].[MYTEST])
> > User: u2 (login: u2) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u2].[MYTEST])
> > User: u3 (login: u3) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u3].[MYTEST])
> > User: u4 (login: u4) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u4].[MYTEST])
> > User: u5 (login: u5) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u5].[MYTEST])
> > User: u6 (login: u6) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u6].[MYTEST])
> > User: u7 (login: u7) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u7].[MYTEST])
> > User: u8 (login: u8) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u8].[MYTEST])
> > User: u9 (login: u9) Table: MYTEST (FullName:
> > [MyServer].[MyDatabase].[u9].[MYTEST])
> >
> > Now, I want to be able to create stored procedure under dbo (let's say
> > dbo.MyStoredProc), which will, when executed read contents of the table
> > for
> > user which is logged in (e.g. through QueryAnalyzer).
> > Basicly this means, that I'm creating procedure:
> > create procedure dbo.MyStoredProc
> > as
> > select * from MYTEST
> >
> > This does not work:
> > create procedure dbo.MyStoredProc
> > as
> > select * from user_name().MYTEST
> >
> >
> > Now, when I login to QueryAnalyzer as u1, I call this SP as:
> > exec dbo.MyStoredProc
> >
> > This will execute SP and read records from dbo.MYTEST. Am I right? How
to
> > achieve to read records from u1.MYTEST?
> > I could write "select * from u1.MYTEST" in SP, but this would require
> > different SP for each distinct user. I want to have only one stored
> > procedure under one user, so if I will have to correct it, I will do so
in
> > one place not for every user that uses this SP. SP is of course much
more
> > complicated that the one used in this post, but I think it is enough to
> > show
> > problem... And what if add some more users... The number of SP's will
only
> > grow.
> >
> > I need something like: select * from @current_loggedon_user.MYTEST? Is
> > this
> > possible in SQL Server (or any database, for that matter) at all? I know
> > that I can construct dynamic query and run it with sp_executesql, but I
> > wonder if there is any other possible solution?
> >
> > Any help greatly appreciated!
> >
> >
> > regards, Jure
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>

"Jure Bogataj" <jure.boga***@mikrocop.com> wrote in message
news:O5tgOEPxFHA.2076@TK2MSFTNGP14.phx.gbl...
> First of all, thank you for your prompt response.
>
> Now I have additional question(s): ;)
> I've noticed sp_MS_marksystemobject is not documented in BOL. Is maybe
> this
> why you would not rely on it? Is this some kind of internal procedure for
> SQL Server? Where could I find some documentation regarding
> sp_MS_marksystemobject (why it is used, can I use it, will it change with
> the next version of SQL Server, etc.)?
>
> TIA
>
>
> regards,
>  Jure
>
>
> "Uri Dimant" <u***@iscar.co.il> wrote in message
> news:uP4CXROxFHA.1148@TK2MSFTNGP11.phx.gbl...
>> Jure
>> I think your approach is wrong
>>
>> Look at this script helps you ( I would not rely on
>> sp_MS_marksystemobject
>> in the production)
>>
>> use master
>> create table t(c1 varchar(50)) insert t values('master')
>> go
>> create proc sp_test as select * from t
>> GO
>> use northwind
>> create table t(c1 varchar(50)) insert t values('northwind')
>> use pubs
>> create table t(c1 varchar(50)) insert t values('pubs')
>> use pubs
>> exec sp_test --returns 'master'
>> use master
>> exec sp_MS_marksystemobject sp_test
>> use pubs
>> exec sp_test --returns 'pubs'
>> use northwind
>> exec sp_test --returns 'northwind'
>>
>>
>> "Jure Bogataj" <jure.boga***@mikrocop.com> wrote in message
>> news:eU4veNOxFHA.2228@TK2MSFTNGP11.phx.gbl...
>> > Hello all!
>> >
>> > I'm having problems regarding executing stored procedures under
> differents
>> > users or different database. I have, let's say 100 tables with the same
>> > name, but different owners. Picture this scenario:
>> >
>> > User: dbo (login: sa) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[dbo].[MYTEST])
>> >
>> > User: u1 (login: u1) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u1].[MYTEST])
>> > User: u2 (login: u2) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u2].[MYTEST])
>> > User: u3 (login: u3) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u3].[MYTEST])
>> > User: u4 (login: u4) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u4].[MYTEST])
>> > User: u5 (login: u5) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u5].[MYTEST])
>> > User: u6 (login: u6) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u6].[MYTEST])
>> > User: u7 (login: u7) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u7].[MYTEST])
>> > User: u8 (login: u8) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u8].[MYTEST])
>> > User: u9 (login: u9) Table: MYTEST (FullName:
>> > [MyServer].[MyDatabase].[u9].[MYTEST])
>> >
>> > Now, I want to be able to create stored procedure under dbo (let's say
>> > dbo.MyStoredProc), which will, when executed read contents of the table
>> > for
>> > user which is logged in (e.g. through QueryAnalyzer).
>> > Basicly this means, that I'm creating procedure:
>> > create procedure dbo.MyStoredProc
>> > as
>> > select * from MYTEST
>> >
>> > This does not work:
>> > create procedure dbo.MyStoredProc
>> > as
>> > select * from user_name().MYTEST
>> >
>> >
>> > Now, when I login to QueryAnalyzer as u1, I call this SP as:
>> > exec dbo.MyStoredProc
>> >
>> > This will execute SP and read records from dbo.MYTEST. Am I right? How
> to
>> > achieve to read records from u1.MYTEST?
>> > I could write "select * from u1.MYTEST" in SP, but this would require
>> > different SP for each distinct user. I want to have only one stored
>> > procedure under one user, so if I will have to correct it, I will do so
> in
>> > one place not for every user that uses this SP. SP is of course much
> more
>> > complicated that the one used in this post, but I think it is enough to
>> > show
>> > problem... And what if add some more users... The number of SP's will
> only
>> > grow.
>> >
>> > I need something like: select * from @current_loggedon_user.MYTEST? Is
>> > this
>> > possible in SQL Server (or any database, for that matter) at all? I
>> > know
>> > that I can construct dynamic query and run it with sp_executesql, but I
>> > wonder if there is any other possible solution?
>> >
>> > Any help greatly appreciated!
>> >
>> >
>> > regards, Jure
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>

"ML" <M*@discussions.microsoft.com> wrote in message
news:DF9E64C2-8FD5-46BE-8B71-939BB8004860@microsoft.com...
> Well, how did *that* happen? Why so many tables? If the sole purpose of
this
> monstrosity is to partition data among several users, and isolating data,
> then you'd be much better off designing a single table with an additional
> column storing the name of the current user.
>
> To that you'd add a view through all data manipulation should be done,
> something like this:
>
> create view dbo.ViewOfTable
> as
> select <column_list>
>          from dbo.Table
>          where (dbo.Table.UserName = system_user)
>
> Now designing a single procedure is "easy as pie". Not to mention the rest
> of the benefits.
>
> And John will never see or change Mary's data (and vice versa).
>
>
> ML