Owner's Access to table

SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
Then the following:-
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
?
Why can I not access the table connected as Jim - it's owner !!!
?
Jim Bunton
Ok - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -

> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co. uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empty
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> ?
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>
|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.

Owner's Access to table

SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
--
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
--
Then the following:-
--
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
--
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
--
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
'
Why can I not access the table connected as Jim - it's owner !!!
?
Jim BuntonOk - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -

> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co.uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empt
y
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> '
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.

Overhead of Multiple SQL Calls

Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>

Overhead of Multiple SQL Calls

Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)
Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>