CODEWITHSUNDEEP
sql-server-2012azure-sql-database
Sunil
Sunil

Reputation: 21406

How to know if 'contained database authentication' is turned on in SQL Azure?

Is there a query I can run to know if 'contained database authentication' is turned on in a user-defined database in SQL Azure or SQL Server 2014?

I tried the following query but it does have a column for 'contained database authentication'?

select *  from   sys.databases 
where name='myDatbase';

Upvotes: 6

Views: 6119

Answers (3)

Satya_MSFT
Satya_MSFT

Reputation: 1022

Contained users are supported in Sql db v12. Looks like you are on v11 and I don't think there is a catalog view to show the same. Contained databases comes with many other things beyond contained users , for example fixed catalog collation. In Azure logically all the databases are contained. So it made sense enabling contained users. The columns you are referring are for box compatible contained databases, and aren't in Azure.

Upvotes: 0

Sunil
Sunil

Reputation: 21406

The following query will tell if contained database authentication is enabled. In query below, containment = 1 and containment_desc = 'PARTIAL' when this feature is enabled, else these values are 0 and 'NONE'.

So, just run this query in SSMS to know whether this feature is enabled. Replace the database name in query with whatever database you are using. In this case, I have used northwind database.

select containment, containment_desc  from   sys.databases 
where name='northwind'

UPDATE 1:

Below is a screen shot of what I found about this at Containment & Containment_desc column values in sys.databases under MSDN documentation.

enter image description here

Upvotes: 7

Sirisha Chamarthi
Sirisha Chamarthi

Reputation: 1293

It seems supported in Azure V12. I tried this and working fine:

create user user1 with password = 'MyPassw0rd' and then try login to login1. If possibe using contained users is a better option as it avoids round trip the master database which can potentially on a different sql instance

Upvotes: 1

Related Questions