In SQL Server 2014 SP1, I create a contained database and a contained user.
The contained user has the db_datareader role.

When I connect with the object explorer in SSMS to the database as the contained user this error appears when right clicking on a table. How can I fix this?

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed. (Microsoft SQL Server, Error: 15562)

have check authentication is proper ?

Yes it is. Executing a SELECT from a table works.

Hi,

There is difference between "database user" and "instance login" (which we usually called user as well). Is your user based on logins?

The user is created with a statement like this in the partial contained user database

CREATE USER [test] WITH PASSWORD=N'xxx'

This user does not have a login. I call this a 'contained' user.

Can you try and connect using SSMS as the contained user?

OK, this should not behave like this :-)

* you are connected to the database, right.

Please check this link and try to go step by step. maybe I am missing something in your explanation: http://www.databasejournal.com/features/mssql/contained-database-authentication-in-sql-server-2012.html

OK i think I understand what you see :-)

you do not get it when you click the tables but when click specific table. This is the expected behave :-)

That is what I did.

Here is the screenshot :-)

If this is what you get then this is normal! just click ok and continue using select query

I hope this is useful :-)

Really? This is not very good... Users complain about it. I can prevent it by setting the database to Trustworthy, but that is not secure.

I think the issue here is that you have created a user within the Database but not an associated login within the Master Database; and as such the user cannot connect to the SQL Server Database Engine.

In fact the user can connect .

Ok; this could be an issue with extended events; can you check this link out?

https://www.mssqltips.com/sqlservertip/3532/give-sql-server-users-access-to-extended-events/

Yes you are right but this what we called "by design" :-)

this is how Microsoft application architect chose to by design. You can contact them and try to make them change it or just "learn how to live with it" :-)

This is why I asked you from the start if you use use USER based on LOGIN. you can change and use USER based on LOGIN or USER based on Windows User. each option has it own behave.

contained user usually do not meant to use the SSMS! their main idea is to use in your application. It is actually meant to replace the SQL EXPRESS "user instance".

*if you need any more help or have any issue please ask but I think that this issue is covered, no?

In my opinion this is a bug, and I have filed it on
https://connect.microsoft.com/SQLServer/feedback/details/1587129

The issue is that SSMS submits a batch that retrieves lot of information from SQL Server, among that information from the DMV sys.dm_hadr_cluster. However, this access can fail, and SSMS knows that:

        declare @cluster_name nvarchar(128)
        declare @quorum_type tinyint
        declare @quorum_state tinyint
        BEGIN TRY
            SELECT @cluster_name = cluster_name,
                @quorum_type = quorum_type,
                @quorum_state = quorum_state
            FROM sys.dm_hadr_cluster
        END TRY
        BEGIN CATCH
            IF(ERROR_NUMBER() NOT IN (297,300))
            BEGIN
                THROW
            END
        END CATCH

That is, two errors that indicate that the user does not have permission to access the DNV are ignored, but they should ignore this error.

Just because I have filed this bug on Connect and it is a trivial fix does not mean that you will see a fix in SSMS any time soon. (Originally, the above code only filtered out error 300. They have fixed this, but only in SSMS 2014, not SSMS 2014.) Bugs submitted on Connect are fixed when time permits. (Unless the issue is apparently alarming.)

If this ia a stopping issue for you and you need a fix in reasonable time, you should open a case with Microsoft, but you can refer to my bug.

It is a shared environemtn where I have this problem. Users should not see databases (names) they don't have access to. This can be done by using traditional logins on a server level in combination with

DENY VIEW ANY DATABASE TO public

But the result of this is that users cannot use the object explorer in SSMS. It is possible to use third party tools, but some users demand using SSMS.

Using database users without login seems to be a very good use case for this scenario. Just one database is shown as required.

I think contained users are a very good use case for instances with many databases that should be isolated.

Thanks for this, I will think about it.

It is a shared environment where I have this problem. Users should not see databases (names) they don't have access to. This can be done by using traditional logins on a server level in combination with

DENY VIEW ANY DATABASE TO public

But the result of this is that users cannot use the object explorer in SSMS. It is possible to use third party tools, but some users demand using SSMS.

Using database users without login seems to be a very good use case for this scenario. Just one database is shown as required.

I think contained users are a very good use case for instances with many databases that should be isolated.

• Edited by IJeb Sunday, July 26, 2015 11:01 AM

It is a shared environment where I have this problem. Users should not see databases (names) they don't have access to. This can be done by using traditional logins on a server level in combination with

DENY VIEW ANY DATABASE TO public

But the result of this is that users cannot use the object explorer in SSMS. It is possible to use third party tools, but some users demand using SSMS.

Using database users without login seems to be a very good use case for this scenario. Just one database is shown as required.

I think contained users are a very good use case for instances with many databases that should be isolated.

• Edited by IJeb Sunday, July 26, 2015 11:01 AM

Hi Erland,

I agree that this behavior is bad, and I voted for your connect. But as much as I can see this is a simple result of the database TRUSTWORTHY, and the SSMS way it work (if there is BUG it is in the SSMS). When I say that this is "by-design", I do not mean that this is a good situation but a simple limitation of the design (in this case). You can use "SET TRUSTWORTHY ON" and then you will not get this error, but at the same time you will be able to see all the other databases :-)

* I dont think that Microsoft meant that contained user will use the SSMS, but the external application that the database was created for. I don't remember the link but I do remember that Microsoft documentation said that User Instances are on a deprecation path, since SQL Server 2008. SQL EXPRESS LocalDB and the contained database on regular version should give us all the feature instead. Same as users that uses the "SQL Server EXPRESS user instance" do not suppose to use the SSMS (almost... I found that in some cases I need to find the user instance name and connect it directly, but this is VERY RARE CASES), contained users do not suppose  to use the SSMS (as much as I se

hi IJeb,

if all that you need is to hide databases from other users then you can  use simple DNY on all databases like this:

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

If you still want to use contained users without seen this error then you can use:

ALTER DATABASE Your_Database_name SET TRUSTWORTHY ON;
GO

* you must be sure that you know the meaning of TRUSTWORTHY.

These two options together will give you the behavior that you are looking for. 
Of course by opening TRUSTWORTHY we are missing some of the idea of contain database, and you can just use simple database and users and hide all databases that the user don't gave permissions, in the same way as above. 

* personally I do not agree with your approach and I think that contained users should not use the SSMS, but this is your call :-)

TRUSTWORTHY is a setting with serious security implications. Don't use it, if you don't understand them - and have been able to understand that the security risks do not apply to you. IJeb indicated that he wanted users to only see the database they have access to - in such an environment TRUSTWORTHY is very likely to be a risk. To wit, there are persons who have db_owner rights in a database without being sysadmin.

As for what contained users are intended for... There is a deficiency in SQL Server, because there is no good way to achieve what IJeb is asking for. Revoking or denying VIEW ANY DATABASE means that users will see no databases, unless they happen to own one. (But if there are many users in such a database, only  one can be the owner.) IJeb settled for contained users, and it works - as long as the users only need to access that one database. Contained users is also frequently suggested as a workaround to the problem.

Saying that this behaviour is "by design" is plain nonsense, in my opinion. To start with, if I want to see the context menu in SSMS, it's not apparent why SSMS must access sys.dm_hadr_clusters. No, they were lazy and did not consider all error codes they should ignore. As I mentioned, originally they only checked for error 300, and the check for error 297 was added later (and only in SSMS 2014!). Maybe they should simply ignore all errors - it can't be essential that they can access sys.dm_hadr_clusters to display a context menu.

Hi Ronen,

I don't agree with this part ...

You can use "SET TRUSTWORTHY ON" and then you will not get this error, but at the same time you will be able to see all the other databases :-)

Setting trustworthy on or off does have no impact on the list of databases the users can see.

I recently saw a case where setting trustworthy on gives a serious security risk. If a user has a db_owner role and the database is owned by a user with the sysadmin role, impersonation can be used to do anything on the server.To

To my understanding partial contained databases and users were developed for database-as-a-service platforms like Azure. The database should be isolated from the instance as much as possible, also for the users who need SSMS.

Hi Erland,

Contained users can also access multiple databases, by creating the users in the different databases with the same sid. But when doing does the users have to be kept synchronous in both user databases.

In my specific case it was easier to create a signed stored procedure to access a different user database in a secure way.

So the only real problem for me seems SSMS.

>> TRUSTWORTHY is a setting with serious security implications. Don't use it, if you don't understand them

True :-)

and that is exactly what I said to IJeb "* you must be sure that you know the meaning of TRUSTWORTHY."

Hi Ronen,

I don't agree with this part ...

You can use "SET TRUSTWORTHY ON" and then you will not get this error, but at the same time you will be able to see all the other databases :-)

Setting trustworthy on or off does have no impact on the list of databases the users can see.

I recently saw a case where setting trustworthy on gives a serious security risk. If a user has a db_owner role and the database is owned by a user with the sysadmin role, impersonation can be used to do anything on the server.To

To my understanding partial contained databases and users were developed for database-as-a-service platforms like Azure. The database should be isolated from the instance as much as possible, also for the users who need SSMS.

>> Setting trustworthy on or off does have no impact on the list of databases the users can see.

Check it! it does. You need to logout and next login you will see all the other databases, unless you another limitation. Dont forget to logout and login :-)

** regarding the "security risk" I told you that I will not use this solution! this is just a workaround and not a solution!

You are right. I did not notice this before.

I used this simple test script:

CREATE DATABASE [test] CONTAINMENT = PARTIAL
GO
ALTER DATABASE [test] SET TRUSTWORTHY ON
GO
USE [test]
GO
CREATE USER [test] WITH PASSWORD=N'x'
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO

Another reason not to use TRUSTWORTHY.

• Edited by IJeb Sunday, July 26, 2015 1:46 PM

You are right. I did not notice this before.

I used this simple test script:

CREATE DATABASE [test] CONTAINMENT = PARTIAL
GO
ALTER DATABASE [test] SET TRUSTWORTHY ON
GO
USE [test]
GO
CREATE USER [test] WITH PASSWORD=N'x'
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO

Another reason not to use TRUSTWORTHY.

• Edited by IJeb Sunday, July 26, 2015 1:46 PM

As I said :-)

This is first step that I wrote, and now you can hide all the other database with the second step as I posted above (again to see that the other databases cant be seen you need to LOGOUT and LOGIN).

* and again, I think that you should not use the SSMS for contained user, and if you must then just use as it is with the alert. This is just a workaround THAT WORK and fit your original request, but it has other implications that You must understand before you use it!

Please close the thread :-)
You are welcome to Vote for Useful responses as

I tested to quickly...

The test script should be like this. So there is no relationship betweeen the setting trustworthy and the list of databases seen.

So my statement was right   Setting trustworthy on or off does have no impact on the list of databases the users can see.

USE master
GO
DENY VIEW ANY DATABASE TO public
GO
CREATE DATABASE [test] CONTAINMENT = PARTIAL
GO
ALTER DATABASE [test] SET TRUSTWORTHY ON
GO
USE [test]
GO
CREATE USER [test] WITH PASSWORD=N'x'
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO

Why close the thread if it is not answered?

• Edited by IJeb Sunday, July 26, 2015 2:05 PM

I tested to quickly...

The test script should be like this. So there is no relationship betweeen the setting trustworthy and the list of databases seen.

So my statement was right   Setting trustworthy on or off does have no impact on the list of databases the users can see.

USE master
GO
DENY VIEW ANY DATABASE TO public
GO
CREATE DATABASE [test] CONTAINMENT = PARTIAL
GO
ALTER DATABASE [test] SET TRUSTWORTHY ON
GO
USE [test]
GO
CREATE USER [test] WITH PASSWORD=N'x'
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO

Why close the thread if it is not answered?

• Edited by IJeb Sunday, July 26, 2015 2:05 PM

I dont see anything different in the above code! you just change the order of my steps which has no meaning :-)

* I will remove myself from following this thread, since I dont think we can help you anymore.

Have a great day.

I added the deny view any database in the beginning, the other code is the same (also the order).

In my environment this is off, that is the point of view.

I was confused and posted to quickly.

I was playing with permissions like VIEW SERVER STATE or on the view itself, but that seems impossible to me. Also the newest build of SSMS (2016 CTP 2) has the same problem.

I was playing with permissions like VIEW SERVER STATE or on the view itself, but that seems impossible to me.

Yes, what permissions you play with does not matter. The problem is that as the error message says, the context is not trusted. Since the database was added by someone local in the database, the server cannot vouch for that the user is authorised to do anything on server level.

If you make the database trustworhty, SSMS now works. However, the access the to DMV still fails, but now with one of the errors that the CATCH handler knows to ignore.

I have received feedback on my Connect item and there are some good news:

This issue has been fixed and will be included in the next hotfix for SQL2014 (both RTM and PCU1). Thank you for your report!

Great to know :-)

Thanks for the info