Hello.

In SQL Server, I want to give permissions to a user so he can have access only to his database with a role that restricts it writable. To do this I do 'deny view any database'. Subsequently, the database list "Object Explorer" of the SSMS appears empty, even though he can run queries on the database which he has access.

I want that, when the user logs in, the Object Explorer only shows his own database. I have already tried using the ALTER AUTHORIZATION and GRANT VIEW but without success.

Can anyone help me please?

Adriana

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

• Proposed as answer by Casey KarstMicrosoft employee, Moderator 14 hours 27 minutes ago

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

• Proposed as answer by Casey KarstMicrosoft employee, Moderator 14 hours 37 minutes ago

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Wednesday, August 05, 2015 4:42 PM

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Wednesday, August 05, 2015 4:42 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator 15 hours 2 minutes ago

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Wednesday, August 05, 2015 4:42 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Friday, August 07, 2015 4:06 PM

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Wednesday, August 05, 2015 4:42 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Friday, August 07, 2015 4:06 PM

Correct me if I'm misunderstanding, but I believe there are two parts to your question.

1) Accessing the contents of a database. To grant a someone access to a database, you create a user within that database from the corresponding login (e.g. CREATE USER testuser FROM LOGIN testlogin). Then, you add that user to the db_datawriter/db_datareader roles to grant write/read access (e.g. ALTER ROLE db_datawriter ADD MEMBER testuser). 

2) Viewing the existence of a database. This is orthogonal to accessing the database. You can either prevent the login from seeing any user databases in the SSMS Object Explorer (using DENY VIEW ANY DATABASE), or you can allow the login to see all databases (default). There isn't a more granular option today. Note that if a login can view any database but does not have a corresponding user within a particular database, they will not be able to expand the Object Explorer tree and access the contents of that database. 

Does this help answer your question?

• Proposed as answer by Casey KarstMicrosoft employee, Moderator Wednesday, August 05, 2015 4:42 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Friday, August 07, 2015 4:06 PM

I have to do 'deny view any database' because I want that the user cannot see the databases of other users.
Each user should be able to access only to his own database and see only his database in object explorer.

• Edited by Adriana M. Costa 16 hours 43 minutes ago

I have to do 'deny view any database' because I want that the user cannot see the databases of other users.
Each user should be able to access only to his own database and see only his database in object explorer.

• Edited by Adriana M. Costa 16 hours 53 minutes ago

I have to do 'deny view any database' because I want that the user cannot see the databases of other users.
Each user should be able to access only to his own database and see only his database in object explorer.

• Edited by Adriana M. Costa Monday, August 03, 2015 2:26 PM

I have to do 'deny view any database' because I want that the user cannot see the databases of other users.
Each user should be able to access only to his own database and see only his database in object explorer.

• Edited by Adriana M. Costa Monday, August 03, 2015 2:26 PM

I have to do 'deny view any database' because I want that the user cannot see the databases of other users.
Each user should be able to access only to his own database and see only his database in object explorer.

• Edited by Adriana M. Costa Monday, August 03, 2015 2:26 PM

I have to do 'deny view any database' because I want that the user cannot see the databases of other users.
Each user should be able to access only to his own database and see only his database in object explorer.

• Edited by Adriana M. Costa Monday, August 03, 2015 2:26 PM

Each user is able to access only his own database. However, it is not possible today for each user to see only his database in the Object Explorer -- it's all or nothing with VIEW ANY DATABASE. 

Others have asked about this in the past. Feel free to vote on this issue here: https://connect.microsoft.com/SQLServer/feedback/details/273830