Solved

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

Posted on 2016-11-04
6
61 Views
Last Modified: 2016-11-09
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?
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41875075
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

0
 

Author Comment

by:rayluvs
ID: 41875122
It's dbo; ran scripts, problem persist.
0
 

Author Comment

by:rayluvs
ID: 41875124
No, wait, the schema didn't do it... Will proceed and let u know.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41875134
Yes I shall wait.
0
 

Author Comment

by:rayluvs
ID: 41875615
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.
0
 

Author Closing Comment

by:rayluvs
ID: 41881473
The problem hasn't reoccurred.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question