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'
CREATE USER Smita
FOR LOGIN Smita
WITH DEFAULT_SCHEMA = MySchema
As the user is with the default schema, the table created automatically gets MySchema as schema name
CREATE TABLE NamesIf we issue the command
(id int IDENTITY ,
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.