Thursday, July 14, 2016

Adding a user to a SQL Azure Database

This week i had to create a few new users for a SQL Azure database. I have to do this often, but I can never remember the exact steps that need to be run.

If you've connected to a SQL Azure instance, you'll quickly see that some of the useful UI Options don't exist or work differently. For example, if you want to add a new user, you get a SQL script that just creates the user, but doesn't give them any access to the database:









SQL:


In order to actually give the user access to a database, you need to switch your connection to the database you want to the user to access and run the following command:

CREATE USER [UserName]
FOR LOGIN [UserName]
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'[UserName]'

GO

You can switch out the default schema and role to whatever fits your needs.

After all of that, the user should work, but if you try to connect from SQL Mgmt Studio, you'll get an error that the user does not have access to the Master database. If this wasn't SQL Azure, we could set the default database and we'd be set, but SQL Azure doesn't support that (yet) so we need have 2 options:

Option 1: Give the user Access to the master database:
This can be done by running the "Create User" script from above but on the master database

Option 2: Pick the specific database from SQL Mgmt Studio.
For this option, when you try a new connection to a SQL server, click the "options" button in the lower right of the screen:

Then type in the name of the database and click Connect:

Done!

Tuesday, July 12, 2016

Restart-AzureWebsite: Your Azure credentials have not been set up

I recently ran into an issue when trying to restart an Azure App Service from PowerShell. The command to do it is Restart-AzureWebsite, but when I attempted to run it, I got the following error:

“Restart-AzureWebsite : Your Azure credentials have not been set up or have expired, please run Add-AzureAccount to set up your Azure credentials”

I ran the Add-AzureAccount command but I continued to get the same error.

After some digging I realized that I had more than 1 Azure subscription and the command was using the wrong one when attempting to restart the website.

I ran the following command to retrieve the subscriptions that are cached on my PC:

Get-AzureSubscription

That returns all of the subscriptions for all of the accounts that you’ve added from Azure. I found the subscription that was correct for the website I was attempting to restart and then ran this command:

Select-AzureSubscription -SubscriptionName "MySubscription"

That set the subscription for the current session to the one that contained the website I needed to restart. From there, everything went smoothly.

Enjoy!