Azure SQL Database – Link User to Login

I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I created a login for an Azure SQL Database, but couldn’t log in. I would get this message, which makes perfect sense.

2015-06-23 14_52_41-Connect to Database Engine

Just like an on-premises instance, I need to link a user to the login for access. However, unlike an on-premises SQL Server, I can’t willy-nilly change databases. If I do this:

Use SQLServerCentral GO

I’ll get this message.

2015-06-23 15_00_31-SQLQuery3.sql - mhknbn2kdz.database.windows.net,1433.SQLServerCentral (jt (58))_

I can’t switch, I need to reconnect. In this case, I use the same login (an admin level) and reconnect to a different database. This certainly can make scripts more cumbersome, and imply that your work, whether through T-SQL or PoSh, needs to include the connection strings for the correct database. In fact, I might recommend PoSh over SSMS for this reason.

Once I’ve connected, I can use standard commands. In this case, I’ll use this code:

add user to SQLServerCentral database connect to database CREATE USER sscdev FROM LOGIN sscdev; add a role for ddl admin EXEC sp_addrolemember db_ddladmin, sscdev;

Now when I click connect with my user, I see this:

2015-06-23 15_02_22-SQLQuery4.sql - mhknbn2kdz.database.windows.net,1433.sqlservercentral (sscdev (5

Just what I need.

SQLNewBlogger

This was a second post as part of the previous one. I was adding a user and login as part of a bit of work and when it didn’t work, I captured screen shots and split this part from the last one.

Five minutes writing this one, probably no more than five minutes slower in running the code to capture the images.

You can do this.

References

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.