There is an old quote “A Picture is Worth a Thousand Words”. I believe this quote immensely. Quite often I get phone calls that something is not working if I can help. My reaction is in most of the cases, I need to know more, send me exact error or a screenshot. Until and unless I see the error or reproduce the scenario myself I prefer not to comment. Yesterday I got a similar phone call from an old friend, where he was not sure what is going on. Here is what he said.
“When I try to connect to SQL Server, it lets me connect just fine as well let me open and explore the database. I noticed that I do not see any user created instances but when my colleague attempts to connect to the server, he is able to explore the database as well see all the user created tables and other objects. Can you help me fix it? “
My immediate reaction was he was facing security and permission issue. However, to make the same recommendation I suggested that he send me a screenshot of his own SSMS and his friend’s SSMS. After carefully looking at both the screenshots, I was very confident about the issue and we were able to resolve the issue. Let us reproduce the same scenario and many there is some learning for us.
Issue: User not able to see user created objects
First let us see the image of my friend’s SSMS screen. (Recreated on my machine)
Now let us see my friend’s colleague SSMS screen. (Recreated on my machine)
You can see that my friend could not see the user tables but his colleague was able to do the same for sure. Now I believed it was a permissions issue. Further to this I asked him to send me another image where I can see the various permissions of the user in the database.
My friends screen
My friends colleagues screen
This indeed proved that my friend did not have access to the AdventureWorks database and because of the same he was not able to access the database. He did have public access which means he will have similar rights as guest access. However, their SQL Server had followed my earlier advise on having limited access for guest access, which means he was not able to see any user created objects.
My next question was to validate what kind of access my friend’s colleague had. He replied that the colleague is the admin of the server. I suggested that if my friend was suppose to have admin access to the database, he should request of having admin access to his colleague. My friend promptly asked for the same to his colleague and on following screen he added him as an admin.
You can do the same using following T-SQL script as well.
USE [AdventureWorks2012]
GO
ALTER ROLE [db_owner] ADD MEMBER [testguest]
GO
Once my friend was admin he was able to access all the user objects just like he was expecting.
Please note, this complete exercise was done on a development server. One should not play around with security on live or production server. Security is such an issue, which should be left with only senior administrator of the server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
@Pinal,
db_datareader database role can read all tables. Assiging users to db_owner database role for reading tables would be a big concern :)
~ IM.
Hi Pinal,
Can you please elaborate the difference between SQL Server 2005,2008,2012
pinal I had a strange case . in sql 2008 r2 i have created a database and I have created a sql user with dbo schema. no matter what permission i give hime i wasnt able to see the database on SSMS as that sql user . i checked the effective permission and it has connect . i even gave control for an extra measure but i wasnt able to see the databases on this server . i solved this by giveing this user view any database permission and then only he was able to see the databases. . can you shed some light on this new property . how can i create a user who can only see the databases i have given him permission to.
Hi Pinal , i have faced same problem but i granted db_datareader permission for that user even though he is not able to view the tables but he is able to access the tables. could to please give me proper answer
solved my problem thanks.
We have an issue where a user logs into a SQL application from Wkstn A and everything works fine. When he logs into Wkstn B, he gets errors that he cannot find tables. Very strange.
Hi Pinal,
I could not able to see login accounts in security context (gui) except my login account and SA. Could you please help me what pesmissions one should have to see the logins?
Your current role is public.
Thanks, I was wondering what right I had forgotten, even tried running a permission check query to see where it went wrong. Too bad this didn’t tell me right away:
USE [dbname];
SELECT * FROM fn_my_permissions (‘username’, ‘USER’);
GO
Use [dbname2];
SELECT * FROM fn_my_permissions (‘username’, ‘USER’);
GO
Is it possible to hide all database except one from a user, note that the user only have read permission on the database in which he have acess