Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:

>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell wha
t
>access the public role gives a user?
>Thanks
>Don
>

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
Don
Yes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:

>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>sql

Permissions Problem

I have got a peculiar problem. I am not able to query my tables without db username prefix like username.tablename. Worse, I have logged in as username.

Any ideas on what could be the problem?

Now I have to give queries like select * from celia.orders instead of just select * from orders.

Thank you.
Celiacan't u use the

use databasename
select * from table

if it was permission, you probably won't be able to see the tables.

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