New login cannot retrieve data from db

Hello all. I created the "student2" user account and set the password to
"student2". I then
went to Enterprise Manager and in the class_mgr database and in its
customers table, I gave this student2 user account access to the database
and set all permissions to the table. But when I log in to SQL Server using
this new userlogin, I can access the SQL Server Query Analyzer OK but when I
run the select * from customers; query I get
http://www.jimwrichards.com/images/student2_1.gif"

.. What have I done wrong or overlooked doing right? Thanks in advance for
any help, Jim.
Jim Richards wrote:

> Hello all. I created the "student2" user account and set the password to
> "student2". I then
> went to Enterprise Manager and in the class_mgr database and in its
> customers table, I gave this student2 user account access to the database
> and set all permissions to the table. But when I log in to SQL Server using
> this new userlogin, I can access the SQL Server Query Analyzer OK but when I
> run the select * from customers; query I get
> http://www.jimwrichards.com/images/student2_1.gif"
> . What have I done wrong or overlooked doing right? Thanks in advance for
> any help, Jim.

What is the default database context for the new user? If it is not
the class_mgr database, then maybe your query is trying to access a
different table of the same name, but which isn't allowed to your user?
Joe Weinstein at BEA
|||"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:6AXCd.483$q4.168@.fe1.texas.rr.com...
> Hello all. I created the "student2" user account and set the password to
> "student2". I then
> went to Enterprise Manager and in the class_mgr database and in its
> customers table, I gave this student2 user account access to the database
> and set all permissions to the table. But when I log in to SQL Server
> using
> this new userlogin, I can access the SQL Server Query Analyzer OK but when
> I
> run the select * from customers; query I get
> http://www.jimwrichards.com/images/student2_1.gif"
> . What have I done wrong or overlooked doing right? Thanks in advance for
> any help, Jim.

No idea, but what are the current permissions on the table? You can use
sp_helprotect to find out (see Books Online for more details):

exec sp_helprotect 'dbo.customers'

Also, did you DENY permissions on that table to student2 or a role which
student2 is in; a DENY takes precedence over a GRANT.

Simon|||Thanks Joe. I set the default database to "class_mgr" which contains the
table "customers" which contains just two rows (customers). As soon as I
access the SQL Query Analyzer using this student2 login the current database
is automatically set to "class_mgr" instead of the usual "master" database.

"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:41DC46D5.5040701@.bea.com...
>
> Jim Richards wrote:
>> Hello all. I created the "student2" user account and set the password to
>> "student2". I then
>> went to Enterprise Manager and in the class_mgr database and in its
>> customers table, I gave this student2 user account access to the database
>> and set all permissions to the table. But when I log in to SQL Server
>> using
>> this new userlogin, I can access the SQL Server Query Analyzer OK but
>> when I
>> run the select * from customers; query I get
>> http://www.jimwrichards.com/images/student2_1.gif"
>>
>> . What have I done wrong or overlooked doing right? Thanks in advance for
>> any help, Jim.
> What is the default database context for the new user? If it is not
> the class_mgr database, then maybe your query is trying to access a
> different table of the same name, but which isn't allowed to your user?
> Joe Weinstein at BEA
>>
>|||Thanks Simon. I have a snapshot of the current permissions:
http://www.jimwrichards.com/images/student2_2.gif.

I did NOT deny any permissions, see snapshot:
http://www.jimwrichards.com/images/student2_3.gif.

What now, Sir? Jim.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41dc4a08$1_1@.news.bluewin.ch...
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:6AXCd.483$q4.168@.fe1.texas.rr.com...
>> Hello all. I created the "student2" user account and set the password to
>> "student2". I then
>> went to Enterprise Manager and in the class_mgr database and in its
>> customers table, I gave this student2 user account access to the database
>> and set all permissions to the table. But when I log in to SQL Server
>> using
>> this new userlogin, I can access the SQL Server Query Analyzer OK but
>> when I
>> run the select * from customers; query I get
>> http://www.jimwrichards.com/images/student2_1.gif"
>>
>> . What have I done wrong or overlooked doing right? Thanks in advance for
>> any help, Jim.
>>
>>
> No idea, but what are the current permissions on the table? You can use
> sp_helprotect to find out (see Books Online for more details):
> exec sp_helprotect 'dbo.customers'
> Also, did you DENY permissions on that table to student2 or a role which
> student2 is in; a DENY takes precedence over a GRANT.
> Simon|||"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:QEYCd.502$q4.238@.fe1.texas.rr.com...
> Thanks Simon. I have a snapshot of the current permissions:
> http://www.jimwrichards.com/images/student2_2.gif.
> I did NOT deny any permissions, see snapshot:
> http://www.jimwrichards.com/images/student2_3.gif.
> What now, Sir? Jim.

Good question - everything seems to be correct, unless I've missed something
somewhere. Is student2 in any roles?

exec sp_helpuser 'student2'

If student2 is in the db_denydatareader role for some reason, then that
would explain what you're seeing. If not, then I'd try dropping and
recreating the user again (and running DBCC CHECKDB wouldn't hurt either,
just in case there is some database corruption).

Simon