Solved

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

Posted on 2016-11-04
6
24 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 18

Accepted Solution

by:
Pawan Kumar Khowal 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql to find top3 for each record 7 18
using t-sql EXISTS 8 23
Authentication error 1 0
Restrict result set 1 0
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard 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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now