Azure SQL Database – Common Username for Both Databases

azure-sql-databasesql server

I have 2 azure databases created in one SQL Server. I.e.

Database 1 = db1 in xsqldb01.database.windows.net
Database 2 = db2 in xsqldb01.database.windows.net

I would like to create a username or user login where I can read only data (Select grant only) of both databases. How can I achieve this? Do I need to create a user login with same name in both database?

Best Answer

You can create Login in master database using syntax ..

CREATE LOGIN <SQL_login_name, sysname, login_name> 
    WITH PASSWORD = '<password, sysname, Change_Password>' 
GO

and then create user in individual databases using syntax

CREATE USER <user_name, sysname, user_name>
    FOR LOGIN <login_name, sysname, login_name>
    WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

GRANT SELECT  TO <user_name>

This can be done via SSMS from local machine or VM. I believe you can achieve this using Query Editor in Azure as it does not allow switching context to master database .

Related Solutions

Sql-server – Creating SQL Azure login for single database

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '&lt;strong password here&gt;';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;

EXEC sp_addrolemember 'db_owner', 'YourNewUser';

Azure Database Aliases

Your question appears to be a duplicate of this question, but that doesn't have a formal answer which means it technically can't be a duplicate :) so I'll synthesize an answer here. As you can see in this response on StackOverflow the latest version of the Portal should allow you to set the server name. I copied the picture over from that answer for convenience.

If that doesn't work you can go to this blog post as a fall back. The meat of that blog post is here:

To use a meaningful alias, what you need to do is:

Using SQL Server Configuration Manager, in the SQL Native Client 11.0 Configuration\Aliases node, create an alias. Pick a meaningful name (ie; HRServer) for the Alias Name, leave the port at the default (ie: 1433), leave the protocol at the default (ie: TCP/IP) and provide your real server name (ie yy2195dk1k.database.windows.net) in the Server field.

Create the same alias in the SQL Native Client 11.0 Configuration (32bit)\Aliases node.

When connecting to the server in SSMS, enter your new alias (ie: HRServer) for the Server name, SQL Server Authentication for the Authentication method, and for the login, specify your login name followed by an @ symbol, followed by the first segment of the real server name (ie: mylogin@yy2195dk1k), then enter your password.

If the user isn't an admin user that has the ability to connect to the master database, you'll also need to manually set the database name using the Options button. (Note that you won't be able to browse for database names).

Later in the blog's comments they state:

After all, an alias is just a registry entry and so it can be pushed out by group policy like any other registry entry.

So you should be able to push out a group policy to any other clients which should allow them to use the alias if the Azure portal method doesn't work.

Please post an answer or comment on my answer if you find this doesn't work. My answer is based on research not field experience.

Best Answer

Related Solutions

Sql-server – Creating SQL Azure login for single database

Azure Database Aliases

Related Question