SELECT permission denied

[chris5g]

SELECT permission denied on object 'tblHardware', database 'hec_inventory', schema 'dbo'.

SQL Statement: "SELECT * FROM tblHardware"


Here is my problem...

I can connect to the DB just fine using a login i created. I have the server setup in SQL & Windows authentication mode. I added the login to the database and gave it every permission available and nothing.

I'm using SQLExpress and the MS SQL Server Mgmt Studio Express

-chris
________________________________________
....my other programs are better .. i promise

 

[chris5g]

Well, I got it to work only after I made my login a sysadmin under server roles.

are there any other options?

-chris
_______________________________________

 

[SQLDenis]

in order to select you need to be part of the db_datareader role

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[chris5g]

I already was and no go.
Under User Mappings for my login, I selected the database I wanted to use 'inventory' and then checked everything:

db_accessadmin
db_backupoperator
db_datareader
db_datawriter
...

and still did not work until i made the login a sysadmin

-chris
_______________________________________

 

[SQLDenis]

you don't have a 'hec_inventory' and a 'inventory' DB right?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[chris5g]

they are the same... typo

When you create a login, you have to assign it a server role or not? I changed mine to dbcreator and was still successful.

I've got 2 days w/ SQL Server under my belt

-chris
_______________________________________

 

[chris5g]

I take it back, dbcreator alone did not work, had to change back to sysadmin

-chris
_______________________________________

 

[mutley1]

When you say you checked EVERYTHING, did that include db_denydatareader?

 

[chris5g]

Yes, none of those worked anyway. before i had just db_datareader & db_datawriter alone because of what I had read and it still did not work. Only then did i check everything under the sun to make it work.

Even still, would a Server Role of sysadmin override all these setting anyway?

What are the correct setting for a login to be able to query a database? I'm looking at the Server Role & User Mapping settings under my login properties



-chris
_______________________________________

 

[wbodger]

Within SQL Server, there are 2 levels of security, right? There is the login to get you into the server and then there is the actual database access. So, the login that I use for web based access does not have a Server Role, but it does have public under database access.