Why can a user query database but can't see it and the tables in management studio?

Question

0.00/5 (No votes)

See more:

Hello,
I gave a user (AD user) db_owner permission to a database on SQL Server 2008 R2. And after connected to sql server via management studio 2014 or 2008 R2, user can see only Sytem Databases under Databases in Object Explorer. But can not see the database that I mapped to in Security > Login.

I also tried giving view definition and select permission as well but still user other than quering the tbales cannot see the database and the tables.

Default schema is dbo

Any ideas why?

P.S. user needs only write and read permissions to that Database so that can view the data in the tables and make changes if needed.

What I have tried:

I made the user db_Owner and gave all the other available higher priviligies in SQl Server but still the user cannot see the database and the tables in Object Explorer. I believe that SSM is installed properly as I tried with SSMS 2014 and 2008 R2

After i run this query, i get the list of corrcet tables but cannot see them and the database.

select * from sys.tables

order by name

Posted 18-Feb-16 12:11pm

Member 11829482

Updated 18-Feb-16 18:08pm

v3

Add a Solution

Comments

an0ther1 18-Feb-16 18:22pm

What Server Role did you apply to the User?

Member 11829482 18-Feb-16 18:24pm

public

an0ther1 18-Feb-16 23:14pm

Open SQL Management Studio as a Sysadmin, right-click on the server & select Properties.
Select Permissions page in left panel.
Select the User in the right panel & view the effective permissions.
If you receive an error 0x5 it will be due to the account you are running SQL under not having domain permissions.
I would suggest that SQL may have had the View Any Database flag set to false for the user, to undo this execute the following;
GRANT VIEW ANY DATABASE TO [login_name];

Richard Deeming 19-Feb-16 11:48am

If the user only needs to be able to read and write data in the database, db_owner is the wrong role to chose. It gives them access to change the structure of the database, and to mess up the security for the database.

Instead, give then db_datareader and db_datawriter access. That gives them permission to read and write to any table in the database, without giving them additional permissions that they don't need.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

johnwalker10 · Accepted Answer · 2016-02-18T18:08:00

SELECT permission is required to read data from table

SQL

GRANT SELECT ON OBJECT::dbo.TableName TO UserName;

INSERT permission for writing to a table

SQL

GRANT INSERT ON OBJECT::dbo. TableName TO UserName;

UPDATE permission to change an existing role

SQL

GRANT UPDATE ON OBJECT::dbo. TableName TO UserName;

DELETE permission is required to remove a row

SQL

GRANT DELETE ON OBJECT::dbo. TableName TO UserName;

Check out the following link this will help you to find all permissions for users in a database: SQL Server query to find all permissions/access for all users in a database - Stack Overflow