CODEWITHSUNDEEP
sql-serverazure
Greg Gum
Greg Gum

Reputation: 38109

How to set up SQL Server Principal on Azure SQL database

I am trying to set up a connection string for an MVC website that uses an Azure website, and I am having trouble getting it to connect.

I can connect via SSMS using the SA account. But I would like to create a SQL Server principal that has data-reader, and data-writer privileges.

I have set up a SQL Server principal on Azure according to this.

However, when I try to log in with it using SSMS, I get the following message:

Unable to connect to default database 'Master' Login Failed.

This is the script:

CREATE LOGIN Test WITH PASSWORD='...'
use AskProd
go

Create user Test from login Test
go

exec sp_addrolemember 'db_datareader', 'Test'
exec sp_addrolemember 'db_datawriter', 'Test'

Upvotes: 0

Views: 605

Answers (1)

Francois
Francois

Reputation: 893

With Azure SQL your default database is Master and you can't change it. That is why it worked from the connection string where you specify a database but not from SSMS.

You can specify the database you want to connect to in the SSMS Server connection screen if you click on options.

Or if you want this new user to be able to connect to Master create a user for it in Master along with the login.

Upvotes: 1

Related Questions