Get Missing Objects For a Particular User in a Database

Sometimes a newly created user doesn't find user-created objects in SQL Server. I encountered this situation. I got the issue when I logged in onto a production server from a newly created user, I was not able to see/access any user created objects in the Tables/Views folder and so on. I then logged in as an admin user and all the user-created objects were in the Tables/Views folder and so on. I guessed that it could be a permissions issue.

 

So, I checked the new user permissions that has only public Role.
 

Let us reproduce the same scenario.
 

We can see the missing objects in the following image.

 

 
 

 

We check all objects using the following T-SQL:

SELECT name, object_id FROM sys.tables ORDER BY name

 

 

The result is different when I logged in as an admin user and run the above T-SQL to display the user-created objects.

  

 

  

 

Then I checked the User Mapping Permissions of the newly created user in the database.

 

First New user permissions

 

User Mapping screen

 

 

Now admin permissions

 

User Mapping screen
 

 

 

I found that the new user has only public access, that means the user will have rights similar to that of a guest user, so he will not be able to see any user-created objects. So the new user should be a member of database owner because members of the db_owner fixed database role can perform all configuration and maintenance activities on the database. Now we can add a new user as a member of the database owner as in the following screen.

 

 

 

We can do the same using T-SQL as in the following:

USE [DBATest]
GO
 ALTER ROLE [db_owner] ADD MEMBER [TestUser]
GO

 Now the new user was able to access all the user objects like this: