gatting at my data!

Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose owner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies - I an assuming that this means there is a table called companies whose owner is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
.. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and connect then I can access the table!!! - Select user_name informs me that I'm dbo [I can still access the table whether or not Select . . permissions are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition?
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Trusted Connection (windows authentification] to work for Northwind and resorted to using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton