It’s quite common that Business Analyst will always ask for the permission to access the databases of our systems to do data analysis. However, most of the time we will only give them read-only access. With on-premise MS SQL Server and SQL Management Studio, it is quite easily done. However, how about for those databases hosted on Azure SQL?
Login as Server Admin
To make things simple, we will first login to the Azure SQL Server as Server admin on SQL Management Studio. The Server Admin name can be found easily on Azure Portal, as shown in the screenshot below. Its password will be the password we use when we create the SQL Server.
Create New Login
By default, the master database will be the default database in Azure SQL Server. So, once we have logged in, we simply create the read-only login using the following command.
CREATE LOGIN <new-login-id-here> WITH PASSWORD = '<password-for-the-new-login>' GO
Alternatively, we can also right-click on the “Logins” folder under “Security” then choose “New Login…”, as shown in the screenshot below. The same CREATE LOGIN command will be displayed.
Create User
After the new login is created, we need to create a new user which is associated with it. The user needs to be created and granted read-only permission in each of the databases that the new login is allowed to access.
Firstly, we need to expand the “Databases” in the Object Explorer and then look for the databases that we would like to grant the new login the access to. After that, we right-click on the database and then choose “New Query”. This shall open up a new blank query window, as shown in the screenshot below.
Then we simply need to run the following query for the selected database in the query window.
CREATE USER <new-user-name-here> FROM LOGIN <new-login-id-here>;
Please remember to run this for the master database too. Otherwise we will not be able to login via SQL Management Studio at all with the new login because the master database is the default database.
Grant Read-only Permission
Now for this new user in the database, we need to give it a read-only permission. This can be done with the following command.
EXEC sp_addrolemember 'db_datareader', '<new-user-name-here>';
Conclusion
Repeat the two steps above for the remaining databases that we want the new login to have access to. Finally we will have a new login that can read from only selective databases on Azure SQL Server.
References
- Adding Users to Your SQL Azure Database
- Controlling and granting database access
- How can I change my default database in SQL Server without using MS SQL Server Management Studio?
KOSD, or Kopi-O Siew Dai, is a type of Singapore coffee that I enjoy. It is basically a cup of coffee with a little bit of sugar. This series is meant to blog about technical knowledge that I gained while having a small cup of Kopi-O Siew Dai.
cuteprogramming 