Okay, before you read on, I want you to know that I will be talking about how to add, assign privileges and other user related operations in SQL Database using SSMS. I want to save you some time and not to find out the content of this post is not what you looking for : )
To cut the story short, let’s first understand what is Azure SQL Database. In short sentence from official:
Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as…
OK, I have always been working with Linux and open sources so I am not familiar with Windows product at all. It took me tremendous amount of time to know the basics even as simple as adding and assigning roles to users to Azure SQL Database.
And this frustrated me.
I did search google, even official website from Azure but I realized that I am so junior with Windows to the extend that, deep in my heart I know the content of those blogs are simple enough, but yet I still cannot understand them and achieve what I wanted to.
And this frustrated me more.
After some struggles, I managed to get what I want finally and I thought it would be a good idea to leave something here, to ease those who might encounter same problems as me. Of course, if they could find me here in the first place.
First thing we talk about is SSMS, as mentioned in the first paragraph. In simple words, it is a MS SQL management tool that you use to work with your SQL server. Download and install it for windows OS. For mac, you can use Virtual Studio Code(V.S.C).
Login to Azure SQL Database
Have your SQL database provision in Azure, get your login information from image below and connect it. Your password is inputed while you creating Azure SQL Database.
Configure your IP to be allowed in Azure SQL Database(SQL DB). When you access to the page as below, your IP address would be detected automatically by Azure at location 1, simply hit “Add client IP” and save, that’s it!
Connect to SQL DB using V.S.C At location 3, enter all required information.
Once connected, you will see something like below. We can do some simple queries just to verify if everything is working as expected. As you can see, I queried all logins and users against master database (right click on master and select query).
Explained:
- The logined SQL DB.
- By default, there will be two databases at the beginning, one is master DB and the one you have created, in my case, ystatit.
- Security under ystatit DB, you can find all users in DB scope.
- All available Logins on the DB server, which is DB Server scope.
Before we move on, we must first talk about how authentication and authorization works on SQL DB. We have three things in SQL DB to look at, which are:
- A Login is an individual account in the MASTER database, to which a user account in one or more databases can be linked. With a login, the credential information for the user account is stored with the login.
- A User account is an individual account in ANY database that may be, but does not have to be, linked to a login. With a user account that is not linked to a login, the credential information is stored with the user account.
- Database Role, assigned to user which determines what the user is allowed to do.
In simple words
- Login + User is your authentication
- Roles are your authorization
- Login is created in MASTER database where User is created on INDIVIDUAL databases
- So I would say, Login is server-scope and User is database-scope
With concepts above, let’s get started.
Perform query against MASTER database to create a Login, ystatitdemo.
create login ystatitdemo with password = '4rfv$RFV4rfv';select * from sys.sql_logins;
Next, let’s create an User same name as the Login against YSTATIT database and link the User with Login.
CREATE USER ystatitdemo FOR LOGIN ystatitdemo WITH DEFAULT_SCHEMA = dbo;select top 1 name,createdate from sys.sysusers order by createdate desc;
Lastly, assign db_owner role to User ystatitdemo. According to Azure SQL DB, they have some fixed roles we can leverage.
Let’s check the current assigned roles before assigning.
SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE u.type NOT IN('R', 'G')
ORDER BY UserName desc, RoleName;
Use ALTER ROLE command against YSTATIT Database to assign role.
ALTER ROLE db_owner ADD MEMBER ystatitdemo;
That’s it! Just as simple as that! Now you can use the newly created user to login to the server and perform any action you need to!
If you have trouble login using SSMS, you must configure the DB name which we just created User against to, in to the Option setting of SSMS at location 3.
Hopefully this article does helped you, see you around!
