Solved

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

Posted on 2016-11-04
6
38 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
  • 4
  • 2
6 Comments
 
LVL 28

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 28

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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