perplexed by queries

I have a database of patients visiting a doctor.
Each patient has personal info in a PATIENT table
Each visit has visit info in a VISIT table, linked to the patient table
Each surgery performed has an entry in the SURGERY table, linked to the
visit table (because some visits have multiple surgeries, some have one, and
others have none)
Here's the problem: I need a query that shows each patient's most recent
visit to the doctor.
I made the query, but it would return all visits--but only when surgery was
performed. I guess the null value from a visit with no surgery made it not
return anything. How can I get it to show me only the most recent visit
record, regardless of if surgery was performed? is there some sql code I'm
missing?
Thanks,select *
from patient p join visit v on p.patientid=v.patientid
where v.visitid exists(select * from surgery s where s.visitid=visitid)
-oj
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:8D6AA643-BDE4-4BFA-8235-B77F3D46F3E6@.microsoft.com...
>I have a database of patients visiting a doctor.
> Each patient has personal info in a PATIENT table
> Each visit has visit info in a VISIT table, linked to the patient table
> Each surgery performed has an entry in the SURGERY table, linked to the
> visit table (because some visits have multiple surgeries, some have one,
> and
> others have none)
> Here's the problem: I need a query that shows each patient's most recent
> visit to the doctor.
> I made the query, but it would return all visits--but only when surgery
> was
> performed. I guess the null value from a visit with no surgery made it not
> return anything. How can I get it to show me only the most recent visit
> record, regardless of if surgery was performed? is there some sql code I'm
> missing?
> Thanks,|||PLEASE POST DDL and the query that you are currently using!
Without ANY idea of what your tables look like
or what the column names are,
this is of course a guess, but...
Select Patient, VisitDate
From Visits V Join Patients P
On P.PatientPK = V.PatientFK
Where V.VisitDate =
(Select Max(VisitDate)
From Visits
Where PatientFK = V.PatientFK)
"Scott" wrote:

> I have a database of patients visiting a doctor.
> Each patient has personal info in a PATIENT table
> Each visit has visit info in a VISIT table, linked to the patient table
> Each surgery performed has an entry in the SURGERY table, linked to the
> visit table (because some visits have multiple surgeries, some have one, a
nd
> others have none)
> Here's the problem: I need a query that shows each patient's most recent
> visit to the doctor.
> I made the query, but it would return all visits--but only when surgery wa
s
> performed. I guess the null value from a visit with no surgery made it not
> return anything. How can I get it to show me only the most recent visit
> record, regardless of if surgery was performed? is there some sql code I'm
> missing?
> Thanks,|||oops...forgot the 'last visit' bit...
select *
from patient p join visit v on p.patientid=v.patientid
where v.visitid exists(select * from surgery s where s.visitid=visitid)
and v.visitdate=(select max(v2.visitdate) from visit v2 where
v2.patientid=v.patientid)
-oj
"oj" <nospam_ojngo@.home.com> wrote in message
news:ex%231W3VRFHA.3628@.TK2MSFTNGP12.phx.gbl...
> select *
> from patient p join visit v on p.patientid=v.patientid
> where v.visitid exists(select * from surgery s where s.visitid=visitid)
> --
> -oj
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:8D6AA643-BDE4-4BFA-8235-B77F3D46F3E6@.microsoft.com...
>|||You wouldn't mention surgery if it was irrelevant, so my guess is
select
PATIENT.*, VISIT.*, SURGERY.*
from PATIENT join VISIT
on PATIENT.PatientID = VISIT.PatientID
left outer join SURGERY
on SURGERY.VisitID = VISIT.VisitID
where VISIT.VisitID = (
select top 1 VisitID
from VISIT
where VISIT.PatientID = PATIENT.PatientID
)
Of course this is all a guess, since you posted no details.
What you were probably missing was the outer join, so
patient visits are returned whether or not there was an
associated surgery.
Steve Kass
Drew University
Scott wrote:

>I have a database of patients visiting a doctor.
>Each patient has personal info in a PATIENT table
>Each visit has visit info in a VISIT table, linked to the patient table
>Each surgery performed has an entry in the SURGERY table, linked to the
>visit table (because some visits have multiple surgeries, some have one, an
d
>others have none)
>Here's the problem: I need a query that shows each patient's most recent
>visit to the doctor.
>I made the query, but it would return all visits--but only when surgery was
>performed. I guess the null value from a visit with no surgery made it not
>return anything. How can I get it to show me only the most recent visit
>record, regardless of if surgery was performed? is there some sql code I'm
>missing?
>Thanks,
>|||Oops - forgot the ORDER BY in the subquery:
select
PATIENT.*, VISIT.*, SURGERY.*
from PATIENT join VISIT
on PATIENT.PatientID = VISIT.PatientID
left outer join SURGERY
on SURGERY.VisitID = VISIT.VisitID
where VISIT.VisitID = (
select top 1 VisitID
from VISIT
where VISIT.PatientID = PATIENT.PatientID
order by VisitDate desc -- ***This line left out of previous reply***
)
Scott wrote:

>I have a database of patients visiting a doctor.
>Each patient has personal info in a PATIENT table
>Each visit has visit info in a VISIT table, linked to the patient table
>Each surgery performed has an entry in the SURGERY table, linked to the
>visit table (because some visits have multiple surgeries, some have one, an
d
>others have none)
>Here's the problem: I need a query that shows each patient's most recent
>visit to the doctor.
>I made the query, but it would return all visits--but only when surgery was
>performed. I guess the null value from a visit with no surgery made it not
>return anything. How can I get it to show me only the most recent visit
>record, regardless of if surgery was performed? is there some sql code I'm
>missing?
>Thanks,
>

permissions problems creating a linked server

I am trying to create a linked server in the management studio and am getting an error

"A required operation could not be completed. You must be a member of the sysadmin role to perform this operation"

I have tried giving the user rights via

GRANT ALTER ANY LINKED SERVER TO [DOM\user]

as well as adding them to the setupadmin group. No luck.

I can add the linked server via sp_addlinkedserver as the user.

Any ideas?After searching some more I came across this.

http://msdn2.microsoft.com/en-us/library/aa560998.aspx

Which states you must be a member of the sysadmin role to create a linked server to do this via the management tools. Any idea why?

When I script the GUI all of the SP's it calls are available to logins with the setupadmin role or who have been granted access to alter any linked server.

Permissions problem

Hi
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
Any ideas why I cannot connect in enterprise manager.?
ThanksI can however import these tables using a DTS job.!!!
"Chubbly Geezer" wrote:

> Hi
> I have some code that I am testing to read data from a FoxPro file.
> I have copied the FoxPro file locally to test.
> I have created a linked server that points to this file. Enterprise manag
er
> correctly shows the tables available.
> Works fine.!
> However, when I create another linked server pointing towards the network
> files, enterprise manager shows no available tables and my code returns an
> error stating that the table does not exist.
> I have checked the security permissions on the network drive/directories a
nd
> I appear to have full access. I can indeed move, delete etc these files.
> Any ideas why I cannot connect in enterprise manager.?
> Thanks|||Hi
Does the account that SQL Server and Agent run under have permission on the
remote share and files?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Chubbly Geezer" wrote:

> Hi
> I have some code that I am testing to read data from a FoxPro file.
> I have copied the FoxPro file locally to test.
> I have created a linked server that points to this file. Enterprise manag
er
> correctly shows the tables available.
> Works fine.!
> However, when I create another linked server pointing towards the network
> files, enterprise manager shows no available tables and my code returns an
> error stating that the table does not exist.
> I have checked the security permissions on the network drive/directories a
nd
> I appear to have full access. I can indeed move, delete etc these files.
> Any ideas why I cannot connect in enterprise manager.?
> Thanks|||Both services were running under local system. Have changed to my login but
still no joy.
Also in linked server security options I have tried changing them to login
as myself but no luck.
The following 2 lines of code ALSO return table data:
EXEC sp_tables_ex @.table_server='OPERA_SERVER'
EXEC sp_columns_ex @.table_server='OPERA_SERVER', @.table_name='ssale'
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Does the account that SQL Server and Agent run under have permission on th
e
> remote share and files?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Chubbly Geezer" wrote:
>|||Correction.
The following 2 lines of code DO NOT WORK..
EXEC sp_tables_ex @.table_server='OPERA_SERVER'
EXEC sp_columns_ex @.table_server='OPERA_SERVER', @.table_name='ssale'
"Chubbly Geezer" wrote:
> Both services were running under local system. Have changed to my login b
ut
> still no joy.
> Also in linked server security options I have tried changing them to login
> as myself but no luck.
> The following 2 lines of code ALSO return table data:
> EXEC sp_tables_ex @.table_server='OPERA_SERVER'
> EXEC sp_columns_ex @.table_server='OPERA_SERVER', @.table_name='ssale'
>
> "Mike Epprecht (SQL MVP)" wrote:
>sql

Permissions on linked reports with subreport

Need help please...
I have one folder 'RSFOLDER' with all reports and subreports in it. I don't
wont to set any permissons on that folder. This fodler is hidden...
I have second folder. That folder is not hidden, it has user friendly name,
and contains linked reports from folder RSFOLDER.
Users have permissions on linked reports.
When user open some report that contains subreport, report renders fine, but
subreports are missing (Error: Report could not be shown...)
When I set up permissions on RSFOLDER everithing is fine...
Any workaround?
Tnnx,
nikolaCreate another level of folders
/RSFolder <-- User's don't have permission
/RSFolder/folder <-- users have permission to read
Then all the reports will be accessible but user can't directly navigate to
them. Another option is to set the permission on each report explicitly.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nikola Smircic" <nikola.smircic@.netlab.hr> wrote in message
news:e2qzadxpEHA.1308@.TK2MSFTNGP14.phx.gbl...
> Need help please...
> I have one folder 'RSFOLDER' with all reports and subreports in it. I
don't
> wont to set any permissons on that folder. This fodler is hidden...
> I have second folder. That folder is not hidden, it has user friendly
name,
> and contains linked reports from folder RSFOLDER.
> Users have permissions on linked reports.
> When user open some report that contains subreport, report renders fine,
but
> subreports are missing (Error: Report could not be shown...)
> When I set up permissions on RSFOLDER everithing is fine...
> Any workaround?
> Tnnx,
> nikola
>sql

Permissions for network services

I have created a Linked Server to an Access DB on a Novell 6.5 server. The Windows 2003 server with SQL 2005 Server on it is logged in as a Novell user with appropriote rights to the folder where the mdb is stored. The mdb is unsecured.

EXEC sp_addlinkedserver
@.server = 'JetShowData',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'k:\groups\sales\showfiles\2007 Show.mdb'

I have successfully created the link and queried the mdb while logged into SQL as Administrator using Windows Autentication. I have set up permissions for another SQL user account (I think) to use the linked server? I have added the linked server login as folllows:

sp_addlinkedsrvlogin 'JetShowData', false, NULL, 'Admin', NULL

This should allow any user to access the linked server. I did try adding a login for a specific SQL user account (sp_addlinkedsrvlogin 'JetShowData', false, 'cbonallo', 'Admin', NULL) but that did not make any difference.

The error I am getting when I try to either test the connection or run a query is the same one I was getting when I had the MSSQLSERVER service running under a local account( I had to change is so that SQLSERVER had network access rights):

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3027&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

BUTTONS:

OK

This Access db does not have a workgroup file becasue it is unsecured. This error is being reported incorrectly since it is the exact same one when SQL did not have permissions to access the network. I also tried it from another Windows Autenticated user account and got this message:

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3027&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

I'm thinking I need to somehow I need to give the user account network access rights, too? Or some other permissions? Any help will be greatly appreciated!!

Cory Bonallo

Retail Services

Developer

So this is a stumper? There is nothing I can look into. Tech articles? Anything. I did read up on server incompatibilities from this artical http://support.microsoft.com/kb/823659/en-us but don't know enough to evaluate these settings. Any hints would be greatly appreciated!

Cory

Permissions for network services

I have created a Linked Server to an Access DB on a Novell 6.5 server. The Windows 2003 server with SQL 2005 Server on it is logged in as a Novell user with appropriote rights to the folder where the mdb is stored. The mdb is unsecured.

EXEC sp_addlinkedserver
@.server = 'JetShowData',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'k:\groups\sales\showfiles\2007 Show.mdb'

I have successfully created the link and queried the mdb while logged into SQL as Administrator using Windows Autentication. I have set up permissions for another SQL user account (I think) to use the linked server? I have added the linked server login as folllows:

sp_addlinkedsrvlogin 'JetShowData', false, NULL, 'Admin', NULL

This should allow any user to access the linked server. I did try adding a login for a specific SQL user account (sp_addlinkedsrvlogin 'JetShowData', false, 'cbonallo', 'Admin', NULL) but that did not make any difference.

The error I am getting when I try to either test the connection or run a query is the same one I was getting when I had the MSSQLSERVER service running under a local account( I had to change is so that SQLSERVER had network access rights):

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3027&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

BUTTONS:

OK

This Access db does not have a workgroup file becasue it is unsecured. This error is being reported incorrectly since it is the exact same one when SQL did not have permissions to access the network. I also tried it from another Windows Autenticated user account and got this message:

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3027&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

I'm thinking I need to somehow I need to give the user account network access rights, too? Or some other permissions? Any help will be greatly appreciated!!

Cory Bonallo

Retail Services

Developer

So this is a stumper? There is nothing I can look into. Tech articles? Anything. I did read up on server incompatibilities from this artical http://support.microsoft.com/kb/823659/en-us but don't know enough to evaluate these settings. Any hints would be greatly appreciated!

Cory

Permissions for linked server

This is a continuation from another thread in the SSIS forum. The broader issue is a Security/Permissions issue.

We are running SQL Server on a Windows 2003 server that is isolated from our main Novell network. The db admin does not want to load the Netware Client on the server for security reasons. As we continue to develop and extend our data access I am finding, as a developer, that I will need to frequently consume data and files store on the Novell network.

I did get the "net use" command working in a Job as a part of an SSIS package. This took a fair amount of research and trial and error to get working but at least it works. Now I am trying to use the same process to open a connection to Novell in the Query editor so I can setup a stored procedure that a Web service can call to open a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the net use command succeeds but does not make the conncetion availible to any other session. When I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. It works fine in a dos window opened manually. So I started looking at permissions. Whoami returns Administrator from the manual dos window. Whoami returns nt autority from the batch file. Since I have a valid Novell user/password in the net use command why does if fail? When I used impersonate it did not change the user from NT AUTHORITY/? I can't a connection open.

My goal is to find a method to connnect and disconnect to the Novell network as needed from SQL Server as well as from IIS Web applications. My Network Admin does not have any answers for me. I have done a ton of research and so far the only thing that has worked, at least partially, is "net use." Does anyone know how to overcome this issue? I know next to nothing about networking and security and certainly not how to get Windows and Novell to play well together without the Netware Client running. I do have the Gateway for Client Services running but not logged in for the net use function. Any help would be greatly appreciated!

Cory Bonallo

Retail Services

Developer

Please, I am still stuck. Does anyone have a clue on how to resolve this? Is there something different about permissions for the nt authority and administrator accounts at the server level? Something that would present different credentials to the Novell Server when trying to connect?

Cory

|||

Generally,

If your SQL Server service account is Localsystem then you will present credentials of domain\machine_name$ to other systems.

If your SQL Server service account is domain\domain_user then you will present credentials of domain\domain_user to other systems.

For xp_cmdshell there is a proxy account that can be set so the credentials sent out of sql server's process are the proxy accounts instead of the server's when the caller is not a sysadmin. If the caller is a sysadmin then xp_cmdshell will use the server's credentials.

hth,

-Steven Gott

S/DET

SQL Server

|||

Thank you for the reply. I tried changing the cridentials for xp_cmdshell but "whoami" still returns nt authority and the h: and dir commands fail. Why would the proxy account not take? I don't get it. That same batch works fine from the command prompt. The only difference I can see is that "whoami" returns Administrator there. I just don't understand enough to see the difference. The "net use" command succeeds either way, just the drive switch and dir fail.

Cory

|||

If you are calling xp_cmdshell as a sysadmin then the proxy account is not used.

hth,

-Steven Gott

S/DET

SQL Server

|||

Oh. But if I log in as me then I don't have write to execute xp_cmdshell.... hmmm.

Cory

|||I'm having the same problem with two SQLServer 2000 servers in different domains (single hop). sql accounts don't work unless they have system administrator privileges.
Help would be greatly appreciated.|||http://support.microsoft.com/?kbid=830382

apparently, is a bug.

Permission for Linked Server User

Dear,

What minimum permission do i need to grant to a Remote Login to create a linked server.

Regards

Sufian

I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

The minimum permissions for this is alter any login in SQL 2005.

To add the linked Server use the sp_addlinkedserver proc.

The minimum permission for this proc is alter any linked server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

I have created a User say XXX with default database <master> with no other permission apart from Connect.

What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

regards

sufian

|||

I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

I did a quick search and found a related article that may help

http://www.databasejournal.com/features/mssql/article.php/3691721

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steven,

Thanks for the link it helped in better understanding of Linked Server.

My question is still pending regarding the permission to user used in making connection to the remote server?

Well i got the resolution.

We have to grant only two permission to the user to use it in linked server creation.

GRANT CONTROL SERVER TO LinkedServer_User;

GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

regards

Sufian

|||

Sufian,

The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

I am surprised that this doesn't work:

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Can you confirm that this doesn't work? If it doesn't then I can open a bug with the product group.

Thanks,

-Steven Gott

SDE/T

SQL Server

|||

Created a new user abc and given readonly access to one database x only.

No other permission a part from DataReader to abc.

Created new linked server giving the username and password in the (Be made using the security context) option.

Linked server created successfully.

and i can only see the database on which user have read only access.

After granting below mentioed permission no effect on cataloge

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Well i think i need to give access to user on other databases also.

Please open a bug if it is.

Regards

Sufian

Permission for Linked Server User

Dear,

What minimum permission do i need to grant to a Remote Login to create a linked server.

Regards

Sufian

I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

The minimum permissions for this is alter any login in SQL 2005.

To add the linked Server use the sp_addlinkedserver proc.

The minimum permission for this proc is alter any linked server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

I have created a User say XXX with default database <master> with no other permission apart from Connect.

What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

regards

sufian

|||

I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

I did a quick search and found a related article that may help

http://www.databasejournal.com/features/mssql/article.php/3691721

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steven,

Thanks for the link it helped in better understanding of Linked Server.

My question is still pending regarding the permission to user used in making connection to the remote server?

Well i got the resolution.

We have to grant only two permission to the user to use it in linked server creation.

GRANT CONTROL SERVER TO LinkedServer_User;

GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

regards

Sufian

|||

Sufian,

The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

I am surprised that this doesn't work:

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Can you confirm that this doesn't work? If it doesn't then I can open a bug with the product group.

Thanks,

-Steven Gott

SDE/T

SQL Server

|||

Created a new user abc and given readonly access to one database x only.

No other permission a part from DataReader to abc.

Created new linked server giving the username and password in the (Be made using the security context) option.

Linked server created successfully.

and i can only see the database on which user have read only access.

After granting below mentioed permission no effect on cataloge

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Well i think i need to give access to user on other databases also.

Please open a bug if it is.

Regards

Sufian

Permission for Linked Server User

Dear,

What minimum permission do i need to grant to a Remote Login to create a linked server.

Regards

Sufian

I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

The minimum permissions for this is alter any login in SQL 2005.

To add the linked Server use the sp_addlinkedserver proc.

The minimum permission for this proc is alter any linked server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

I have created a User say XXX with default database <master> with no other permission apart from Connect.

What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

regards

sufian

|||

I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

I did a quick search and found a related article that may help

http://www.databasejournal.com/features/mssql/article.php/3691721

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steven,

Thanks for the link it helped in better understanding of Linked Server.

My question is still pending regarding the permission to user used in making connection to the remote server?

Well i got the resolution.

We have to grant only two permission to the user to use it in linked server creation.

GRANT CONTROL SERVER TO LinkedServer_User;

GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

regards

Sufian

|||

Sufian,

The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

I am surprised that this doesn't work:

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Can you confirm that this doesn't work? If it doesn't then I can open a bug with the product group.

Thanks,

-Steven Gott

SDE/T

SQL Server

|||

Created a new user abc and given readonly access to one database x only.

No other permission a part from DataReader to abc.

Created new linked server giving the username and password in the (Be made using the security context) option.

Linked server created successfully.

and i can only see the database on which user have read only access.

After granting below mentioed permission no effect on cataloge

GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

GRANT ALTER ANY LOGIN TO LinkedServer_User;

Well i think i need to give access to user on other databases also.

Please open a bug if it is.

Regards

Sufian