Users cannot access a specific table within their Microsoft SQL Server database

Users on a daily bases works with a database but have problems with one specific table within the database.  Yet, administrator users don’t have problem with the specific table, only non-admin users.  Both users and admin-users have Windows Server username/password and SQL username/password.

How can we fix this?
Who is Participating?
Pawan KumarConnect With a Mentor Database ExpertCommented:
What is the schema of that table ?

dbo or something else? Try below -

CREATE USER [UserNamePawan] FROM LOGIN [LoginNamePawan]
GRANT SELECT ON SCHEMA::SchemaName TO [UserNamePawan]
GRANT UPDATE ON SchemaName.TableName TO [UserNamePawan]
GRANT DELETE ON SchemaName.TableName TO [UserNamePawan]
GRANT INSERT ON SchemaName.TableName TO [UserNamePawan]

Open in new window

rayluvsAuthor Commented:
It's dbo; ran scripts, problem persist.
rayluvsAuthor Commented:
No, wait, the schema didn't do it... Will proceed and let u know.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Pawan KumarDatabase ExpertCommented:
Yes I shall wait.
rayluvsAuthor Commented:
Wasn't successful.

The connection strings tried were:

  • Provider=SQLOLEDB; Initial Catalog=DATABASENAME; Data Source=SERVERNAME; integrated security=SSPI; persist security info=True;

  • Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User ID=sa;Password=BLAHBLAH;

  • Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User ID=USERNAME;Password=BLAHBLAH;

The only one that worked was the 2nd one.

The problem with that is that we have to hard-code the sa password and for obvious reason is not an optimal choice.
rayluvsAuthor Commented:
The problem hasn't reoccurred.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.