Create a read-only user on Azure SQL Server

Database permissions to enable users to either manage data and/or data definitions object like tables, views and other is a key security concern on a database. In some cases, there is the need to allow a user to access the data only. To do that, we need to create a read-only user on the database. With this article, we look at the steps that are needed to create a read-only user on an Azure SQL Server database.

Note: To follow this article, an Azure subscription with a database already deployed is recommended.

Find the Server Information:

We should start this process by first getting the SQL Server instance URL address from the Azure Console. Login the Azure console and select or search for the SQL Server resources.  Look at the results and select the server hosting the database that needs the additional user profile.

After selecting the server, make sure a server is selected not a database instance, we should see the server information overview. From this view, we can find the server URL name and the administration login account. The password is not visible from the view, so we should get that information from our security software like Key Vault or wherever the passwords are kept. We need this information to be able to login into the database remotely, so we can add the new user profile.

But before we login to the server, we also need to add the client IP address to the server firewall configuration. This enables remote client application to reach the database.  From the server information page, search or select “Firewall and virtual networks”.  We can then click the “+ Add Client IP” button. This reads the client IP address from browser. This information is available because we are using a browser to access the console.  This adds our current IP address information to the firewall rules to enable the access. Once that looks correct, we should now press save to make sure the update is made.

Ready to Connect

We should have all the required information to connect to our database remotely using tools like Visual Studio Code (VSCode) or SQL Server Management Studio (SSMS).  VSCode is a development tool that can target many platforms and languages. SSMS is designed specifically to work on data platforms like SQL Server.

Once your preferred tool is open, we are ready to login, we should connect to the target environment and database by using the server URL and admin credentials. Once the connection is set, we can open a query window, so we can add the following code:

Note:  Make sure to change the login, username and target database to match your environment.

 

-- select master database to create the login profile USE master; GO -- creates the login account CREATE LOGIN [rptLogin] WITH password='add-pw-here'; -- DROP LOGIN rptLogin; -- enable the access to login to the database CREATE USER [rptUser] FROM LOGIN [rptLogin] WITH DEFAULT_SCHEMA=[dbo]; -- DROP USER [rptUser]; -- move to the target database context USE mydb; GO -- create the user on the target database CREATE USER [rptUser] FROM LOGIN [rptLogin] WITH DEFAULT_SCHEMA=[dbo]; -- add the data reader role to the user EXEC sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'rptUser'

Read Only User Gist

Before we run the script, let us break down the code and understand what is going on.

Create Login Account

To create an account to access the database, we must first create a “Login” profile on the master database which host the system database configuration and security for all the databases. We do this by first switching to the master database context using the “USE” command. We then create the login profile “rptLogin” with the default schema information “dbo”.  We also add a user profile “rptUser” to the master database from the login recently created to enable the user to login to the server even if not database permission has been granted yet to this user profile. This step is optional. It is more important to create the user in the target database.

Create Database Read-Only User

At this point, there is a login account, but the user cannot access any database. This leads us to the next step. We need to switch context to the target database by using the “USE” command. We can then create the same user “rptUser” under this database security context. This is the step to grant the user access to a database.   To make this user read-only, we need to assign a role. This is done by granting this user the “db_datareader” role under the selected database context.

Now that we have understanding on what the script would do for us, we can execute the script and create the read-only user account. if there are no error messages, we should be able to use the login credentials to access the database.  To login with the new account, open another database connection and use the login credentials “rptLogin”.  Please notice that we need to use the login account as this is the profile with an assigned password.  

Conclusion

In cases when users required to access the database for read-only purposes, we can create user accounts with the “db_datareader” role on a specific database.  With this role, the user would only be able to read data by running Select statements. Any attempt to insert, update or delete operations are not allowed as this requires the “db_datawriter” role. By only granting the “db_datareader” role, we limit the access thus making our database more secure from possible wrong operations.

Thanks for reading

Originally published by ozkary.com

Database permissions to enable users to either manage data and/or data definitions object like tables, views and other is a key security co...

Read Article →