Add Logins, Users, Permissions

Posted by Barac in Azure, SQL Server on Oct 30th, 2017 | 0 comments

After we created our SQL Server and Azure DB now we need to connect to our Azure DB using SSMS.

First of all, we need to add firewall rule to be able to access our Azure SQL Server from our location.

Click on yours Azure SQL Server on Azure portal, and choose Firewall / Virtual Networks.

Then just add your firewall rule and IP Address, or IP range if you want.

After firewall rules have been added, users can connect to a Azure SQL Database using administrator accounts, or as an database owner or database users (If they already exist).

First you need to login to the SQL Database using administrator account

SQL Database > Properties

To be able to create new login on Azure Logical SQL Server you need to use your Azure Admin account.

Use SQL user with administrator permissions and connect to the master database (This is default database).

CREATE LOGIN [azure_db_user] WITH PASSWORD=N'password'
GO

To be able to do create new database user for that login you must be connected as an admin to the database where you want to create that user.

On Azure SQL you cannot set the default DB for the login, so master database will always be default database. So instead of default you need to choose your database name.

CREATE USER azure_db_user
FOR LOGIN azure_db_user
GO
EXEC sp_addrolemember N'db_owner', N'azure_db_user'
GO

Now, we can try to connect to that db using SSMS specifying default DB for that user.

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

Azure / SQL Server / Azure DB / Add Logins, Users, Permissions

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud