background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================
ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
and[vbcol=seagreen]
> and
> query
> ServerA,
> allow
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================
> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
login[vbcol=seagreen]
> as
> al)
> then
> 'sa'
> and
Chris,[vbcol=seagreen]
be[vbcol=seagreen]
>
Showing posts with label background. Show all posts
Showing posts with label background. Show all posts
Monday, March 19, 2012
how to set up a secured linked server?
background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank you
Steve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>
|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================
ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'[vbcol=seagreen]
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
and
> and
> query
> ServerA,
> allow
>
|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject[vbcol=seagreen]
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================
> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
login[vbcol=seagreen]
> as
> al)
> then
> 'sa'
> and
Chris,[vbcol=seagreen]
be
>
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank you
Steve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>
|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================
ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'[vbcol=seagreen]
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
and
> and
> query
> ServerA,
> allow
>
|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject[vbcol=seagreen]
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================
> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
login[vbcol=seagreen]
> as
> al)
> then
> 'sa'
> and
Chris,[vbcol=seagreen]
be
>
how to set up a secured linked server?
background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > background sql2k on nt5.
> >
> > sorry to post this again. didn't get a good response for my last post
and
> > really need some advice on this.
> >
> > I have read that subject in SQL BOL.
> > and it doesn't make much sense to me.
> >
> > If I were to have a NT group called sqladmins, and i assign Bob, Chris,
> and
> > Doug to that group.
> > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> query
> > ServerB (any database, such as sysobjects in master database) from
> ServerA,
> > but not for other users not belong to that group.
> > What should I do?
> >
> > the bottom line is, I'd like to set up linked server securely and only
> allow
> > certain users in certain NT group to use it. (since sa password will be
> > supplied when set up a linked server, i don't want any user to use an
> > established linked server as they were sa to other servers). is that
> > possible?
> >
> > Pls advise! Thank you
> >
> >
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> > Steve,
> >
> > Using sp_addlinkedserverlogin you could have four logins.
> >
> > 1-3 = Bob, Chris, and Doug as such:
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> >
> > 4 = for everybody else
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to
login
> as
> > themselves.
> >
> > Or, you could only use line 4 and then make the sysadmins group (Bob, et
> al)
> > also sysadmins on the other server. If you are letting them in as 'sa'
> then
> > they are being sysadmins. (Insert Here: Standard advice to not use the
> 'sa'
> > account.)
> >
> > Russell Fields
> >
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > > background sql2k on nt5.
> > >
> > > sorry to post this again. didn't get a good response for my last post
> and
> > > really need some advice on this.
> > >
> > > I have read that subject in SQL BOL.
> > > and it doesn't make much sense to me.
> > >
> > > If I were to have a NT group called sqladmins, and i assign Bob,
Chris,
> > and
> > > Doug to that group.
> > > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> > query
> > > ServerB (any database, such as sysobjects in master database) from
> > ServerA,
> > > but not for other users not belong to that group.
> > > What should I do?
> > >
> > > the bottom line is, I'd like to set up linked server securely and only
> > allow
> > > certain users in certain NT group to use it. (since sa password will
be
> > > supplied when set up a linked server, i don't want any user to use an
> > > established linked server as they were sa to other servers). is that
> > > possible?
> > >
> > > Pls advise! Thank you
> > >
> > >
> >
> >
>
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > background sql2k on nt5.
> >
> > sorry to post this again. didn't get a good response for my last post
and
> > really need some advice on this.
> >
> > I have read that subject in SQL BOL.
> > and it doesn't make much sense to me.
> >
> > If I were to have a NT group called sqladmins, and i assign Bob, Chris,
> and
> > Doug to that group.
> > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> query
> > ServerB (any database, such as sysobjects in master database) from
> ServerA,
> > but not for other users not belong to that group.
> > What should I do?
> >
> > the bottom line is, I'd like to set up linked server securely and only
> allow
> > certain users in certain NT group to use it. (since sa password will be
> > supplied when set up a linked server, i don't want any user to use an
> > established linked server as they were sa to other servers). is that
> > possible?
> >
> > Pls advise! Thank you
> >
> >
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> > Steve,
> >
> > Using sp_addlinkedserverlogin you could have four logins.
> >
> > 1-3 = Bob, Chris, and Doug as such:
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> >
> > 4 = for everybody else
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to
login
> as
> > themselves.
> >
> > Or, you could only use line 4 and then make the sysadmins group (Bob, et
> al)
> > also sysadmins on the other server. If you are letting them in as 'sa'
> then
> > they are being sysadmins. (Insert Here: Standard advice to not use the
> 'sa'
> > account.)
> >
> > Russell Fields
> >
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > > background sql2k on nt5.
> > >
> > > sorry to post this again. didn't get a good response for my last post
> and
> > > really need some advice on this.
> > >
> > > I have read that subject in SQL BOL.
> > > and it doesn't make much sense to me.
> > >
> > > If I were to have a NT group called sqladmins, and i assign Bob,
Chris,
> > and
> > > Doug to that group.
> > > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> > query
> > > ServerB (any database, such as sysobjects in master database) from
> > ServerA,
> > > but not for other users not belong to that group.
> > > What should I do?
> > >
> > > the bottom line is, I'd like to set up linked server securely and only
> > allow
> > > certain users in certain NT group to use it. (since sa password will
be
> > > supplied when set up a linked server, i don't want any user to use an
> > > established linked server as they were sa to other servers). is that
> > > possible?
> > >
> > > Pls advise! Thank you
> > >
> > >
> >
> >
>
how to set up a cube for pivot table service in Excel?
background: sql2k and analysis service
I know how to create a cube in analysis service, but how to view the
cube in the Pivot Table services in Excel?
when I go to Data|PivotTable and PivotChart report...|External Data
Source|
Get Data | OLAP cube, then browse to the server, but I don't see any of
the cubes being created.
I'm using Standard SQL2k. Is this a sql version issue or should i
create cube differently?
thank youHave you set permissions on the cube to allow access from the user running
Excel?
Under Database Roles in Analysis manager.
Standard version includes analysis services, with EE having increased
features in some areas, so I do not think that is your problem.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107205552.456050.61110@.f14g2000cwb.googlegroups.com...
> background: sql2k and analysis service
> I know how to create a cube in analysis service, but how to view the
> cube in the Pivot Table services in Excel?
> when I go to Data|PivotTable and PivotChart report...|External Data
> Source|
> Get Data | OLAP cube, then browse to the server, but I don't see any of
> the cubes being created.
> I'm using Standard SQL2k. Is this a sql version issue or should i
> create cube differently?
> thank you
>|||i'm very lost...:(
i created a cube on the analysis service then add my nt accout to the
All Users database role. (enforced on client).
i then go thru the steps in Excell, Data|PivotTable and PivotChart
report...|External Data Source|
Get Data | OLAP cube, but then where shoudl browse to from there?
i check the book online about the local cube. it was not helping. i
still dont' know how to create a local cube.|||Steve, it is difficult to follow your description, but Local cube has
nothing to do with connecting to an analysis services cube.
After you select olap cube you should be able to select thser server and
database that contains the cube.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107212127.396318.211160@.f14g2000cwb.googlegroups.com...
> i'm very lost...:(
> i created a cube on the analysis service then add my nt accout to the
> All Users database role. (enforced on client).
> i then go thru the steps in Excell, Data|PivotTable and PivotChart
> report...|External Data Source|
> Get Data | OLAP cube, but then where shoudl browse to from there?
> i check the book online about the local cube. it was not helping. i
> still dont' know how to create a local cube.
>|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?
I know how to create a cube in analysis service, but how to view the
cube in the Pivot Table services in Excel?
when I go to Data|PivotTable and PivotChart report...|External Data
Source|
Get Data | OLAP cube, then browse to the server, but I don't see any of
the cubes being created.
I'm using Standard SQL2k. Is this a sql version issue or should i
create cube differently?
thank youHave you set permissions on the cube to allow access from the user running
Excel?
Under Database Roles in Analysis manager.
Standard version includes analysis services, with EE having increased
features in some areas, so I do not think that is your problem.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107205552.456050.61110@.f14g2000cwb.googlegroups.com...
> background: sql2k and analysis service
> I know how to create a cube in analysis service, but how to view the
> cube in the Pivot Table services in Excel?
> when I go to Data|PivotTable and PivotChart report...|External Data
> Source|
> Get Data | OLAP cube, then browse to the server, but I don't see any of
> the cubes being created.
> I'm using Standard SQL2k. Is this a sql version issue or should i
> create cube differently?
> thank you
>|||i'm very lost...:(
i created a cube on the analysis service then add my nt accout to the
All Users database role. (enforced on client).
i then go thru the steps in Excell, Data|PivotTable and PivotChart
report...|External Data Source|
Get Data | OLAP cube, but then where shoudl browse to from there?
i check the book online about the local cube. it was not helping. i
still dont' know how to create a local cube.|||Steve, it is difficult to follow your description, but Local cube has
nothing to do with connecting to an analysis services cube.
After you select olap cube you should be able to select thser server and
database that contains the cube.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107212127.396318.211160@.f14g2000cwb.googlegroups.com...
> i'm very lost...:(
> i created a cube on the analysis service then add my nt accout to the
> All Users database role. (enforced on client).
> i then go thru the steps in Excell, Data|PivotTable and PivotChart
> report...|External Data Source|
> Get Data | OLAP cube, but then where shoudl browse to from there?
> i check the book online about the local cube. it was not helping. i
> still dont' know how to create a local cube.
>|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?
Wednesday, March 7, 2012
how to set image as background image to report
i want to set background image to report can any one tell how to set to whole report. i can set image to header, fotter, details, etc but cannot set for whole reportif you have CR XI, there are some examples of templates, one of the templates is a confidential underlay. That is a least one way to do this. also look for underlay following sections (in section expert) add your graphic to a page header, and select underlay following sections.|||Try this:
Insert another Page Header section (PHb), expand this new section that it would be the size of your page. Place your picture in the center of the PHb.
Then in the Section Expert, select the PHb section and check the "Underlay Following Sections".
Insert another Page Header section (PHb), expand this new section that it would be the size of your page. Place your picture in the center of the PHb.
Then in the Section Expert, select the PHb section and check the "Underlay Following Sections".
Subscribe to:
Posts (Atom)