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.
 
 missing objects
 
 
We check all objects using the following T-SQL:

SELECT name, object_id FROM sys.tables ORDER BY name

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

admin user
  
 
 Object Name 
 
Then I checked the User Mapping Permissions of the newly created user in the database.
 
First New user permissions
 
User Mapping screen
 
User Mapping
 
Now admin permissions
 
User Mapping screen
 
 admin permissions
 
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.
 
 database owner
 
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:
 
 user objects


Similar Articles