Skip to main content

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!

Comments

  1. Appreciating the persistence you put into your blog and detailed information you provide.
    very nice blog it was useful.
    MS Azure Online Training

    ReplyDelete

Post a Comment

Popular posts from this blog

Executing .ps1 files in a DockerFile

This week I was trying to containerize an existing java application. Part of "installing" the application  on the container required executing an PowerShell script in the container during the Image build. Based on the documentation here  I thought i could add the following command to my dockerfile and it would work: RUN install.ps1 However, when I went to build the image, it just hung on that step. I tried several other variations of the run command including: RUN ["Powershell", ".\install.ps1"] which resulted in the following error: '["Powershell"' is not recognized as an internal or external command,operable program or batch file. RUN ["Powershell.exe", ".\install.ps1"] which returned the same error as above. I was about to give up and move the PowerShell commands from the .ps1 file directly into the dockerfile itself as described here , but I had an "A HA!" moment and decided to give a simpler a

Get NodeAuthorization working in Kubernetes with acs-engine

Node Authorization in k8s I'm starting to get into the container world and I'm loving it. Recently we helped a client build out and deploy a micro-services application in Kubernetes. We created the cluster in Azure using the open source project  acs-engine . After we got the cluster set up, our client asked for some updates to the cluster for security reasons. One of those updates was to enable Node Authorization . What is Node Authorization? Node Authorization locks down each Node in the cluster to only be able to do actions on itself. If this is not turned on, its possible for a malicious pod to take actions on any other node, including reading secrets, deleting pods, etc. There is an excellent post by Antoine Cotten that explains this very well ( as well as RBAC, which is a different subject altogether). How do I set it up? Based on the current documentation, it looks like setting up Node Authorization should be easy. Basically follow these steps Turn on TLS

Build/Deploy Windows service with TFS

Building and deploying a web service or website via TFS is pretty straight forward, but automatically deploying a windows service or console application takes a b it of setup. I’ve outlined the steps below to get your service deployed. Step 1: Set up Automated Build and MSDeploy on the Target server. If you are doing any sort of automated build/deploy I recommend using MSDeploy. You can follow steps 1-3 from a previous post here . Step 2: Edit the .csproj file The project file for the app you are trying to deploy needs to be edited so that TFS will create a directory for the output of the project. Otherwise the project will be built, but the assemblies will be placed in the code drop location with no organization to it. To edit the file, go to the open file dialog and locate the csproj file. Select it but don’t click “Open. Instead click on the dropdown arrow next to the Open button, select “Open with”, and choose the XML (Text) editor as shown: Once the file is open, add the fo