SQL Data Services (SDS) New Functionality with SQL Server 2008 R2

Previously when connecting to SQL Azure with SQL Server Management Studio, we had to cancel the default connection to database engine and later choose the New Query option. Refer to post Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

Not anymore. With SQL Server 2008 R2 (Nov CTP) we can connect to the database engine and see the list of objects (databases, tables etc) in the object explorer for SQL Azure.

The figure shows the list of databases, tables within a database and a new user ‘Smita’ added to NewDB database.

For adding a Login and a User, we need to specify the T-SQL Statements as follows:

CREATE LOGIN Smita
    WITH PASSWORD = 'Pa$$w0rd'
GO
CREATE USER Smita
    FOR LOGIN Smita
    WITH DEFAULT_SCHEMA = MySchema
GO

As the user is with the default schema, the table created automatically gets MySchema as schema name

CREATE TABLE Names
(id int IDENTITY ,
[Name] nvarchar(50))

If we issue the command

SELECT * FROM Names

by any other user whose default schema is not MySchema, we get error as follows:

Hence the query needs to be given as:

SELECT * FROM MySchema.Names  

so as to avoid errors.