Grant permissions to specific Database

Copper Contributor

I'm trying to do this:

  • User_1 can't see all databases
  • User_1 can see only specific databases
  • User_1 can do whatever he wants on specific databases

 

I already tried these solutions:

  • Revoke see all database to public role (it works for the first issue)
  • User_1 is db_owner (EXEC sp_changedbowner) (it works for the second issue but he can't operate on db)

Thank you

2 Replies

@MEspo94 , that's not possible to achieve.

 

A user can "see" all databases, doesn't mean he can access all.

If you revoke "View all databases" permission, then the user can "see" on the database he is currently connected to, no others, even not databases he has permissions for.

1- Security --> Logins --> New Login. Default Database: Master (for the connection permission)
2- On the same screen (Login - New) --> User Mapping --> Select the databases which you access
3- For each databases that you select, you can select dbowner on the same screen. This (dbowner) provide the user what ever he wants

Other ways may be confuse you.