By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

New login cannot retrieve data from db

P: n/a
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.
Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a


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


Jul 23 '05 #2

P: n/a

"Jim Richards" <JW********@satx.rr.com> wrote in message
news:6A**************@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
Jul 23 '05 #3

P: n/a
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" <jo*******@bea.com> wrote in message
news:41**************@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

Jul 23 '05 #4

P: n/a
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" <sq*@hayes.ch> wrote in message
news:41**********@news.bluewin.ch...

"Jim Richards" <JW********@satx.rr.com> wrote in message
news:6A**************@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

Jul 23 '05 #5

P: n/a

"Jim Richards" <JW********@satx.rr.com> wrote in message
news:QE**************@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
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.