SQL Server data is read by BI tools to create graphs.
In that case, we did not want to use a user with write permission because the data would only be read and various people would create graphs, so we created a read-only user for sharing.
This was my first time touching user-related issues in SQL Server, so as a reminder, I have included a quick summary of SQL Server users and how to add a read-only user to SQL Server (Azure SQL Database).
structure
Basic
SQL Server has two types of accounts: "login" and "user.
A "login" is an ID used to connect to SQL Server from the outside, also known as "SQL Server authentication.
Then, by associating a "login" with a "user," the database is accessed by the associated "user" when logging into SQL Server from the outside.
association
A "login" can be associated with a "user" in multiple databases, allowing access to multiple databases with a single login.
However, a "login" can only be associated with one "user" per database, one-to-one.
authority
Permissions for what can be done with the database are granted to "users", not to "logins".
The quickest way to grant privileges to a "user" is to assign a "role" to the "user" since there is a "role" that is a set of several privileges.
There are "roles" for each database, as well as built-in "roles", which are listed below.
configuration
The ID and password information for "Login" is registered in the master database of SQL Server.
Based on the above, the procedure for adding a new read-only user is as follows
procedure
Create Login
Connect to the master database with the administrator account and execute the query.
For Azure SQL Database, there is no way to connect to the master database and execute queries from the Azure Portal,
Therefore, you need to connect to the master database from a client such as SQL Server Management Studio or VSCode's SQL Server Extension and execute the query.
CREATE LOGIN <login_name> WITH PASSWORD='<password>'
You can delete your login at
DROP LOGIN <login_name>
Database User Creation
Next, reconnect to the database you wish to access, add a "user" to that database, and link it to the "login".
CREATE USER <user_name> FROM LOGIN <login_name>
The login name and user name can be different.
Users can be deleted at
DROP USER <user_name>
Granting users with privileges (roles)
Assign privileges (roles) to the "users" you have created.
In this case, we wanted a read-only user, so we assigned the "db_datareader" (read data) role.
ALTER ROLE <role_name> ADD MEMBER <user_name>
Deletion of assigned roles can be done at
ALTER ROLE <role_name> DROP MEMBER <user_name>
Built-in roles are listed below.
You can now create a read-only "user" (SQL Server authentication).
Provide access to multiple databases
By "creating users & linking logins" and "assigning roles to users" for each database you wish to access, you can access multiple databases.
Change Password
To change the password, connect to the master database with the administrator account and do the following
ALTER LOGIN <login_name> WITH PASSWORD='<password>'
Confirmation (list view)
For confirmation purposes, the following is a description of how the set data is displayed.
Login List
The list of logins can be viewed by connecting to the master database and performing the following query
SELECT * FROM sys.sql_logins
List of Users and Roles
The list of user roles can be viewed by connecting to the database from which you want to see the list and using the following query
SELECT * FROM sys.database_principals
User roles as well as other information will be displayed.
- Column "type_desc" is "SQL_USER" is the user
- Column "type_desc" is "DATABASE_ROLE" is role
record.
List of users in role
To see what roles are assigned to a user, connect to the database where you want to see the list and use the following query
SELECT * FROM sys.database_role_members
Role users are listed by "principal_id" rather than name, so complete the name by checking it against the "principal_id" in the aforementioned list of users and roles.
List of logins and user ties
The "sid" of the "user" refers to the "sid" of the associated "login," so that we know which "login" is associated with which "user.
This also shows that "login" and "user" are one-to-one correspondence.
The list of ties is a query that spans databases and was a hassle in Azure SQL Database, so we omitted it here.
How to add a new user
After all this writing, the above is the traditional method of adding users, and a new method has recently been recommended.
Background of conventional methods
Originally, SQL Server was installed on a PC and operated in such a way as to create several databases within a single SQL Server. In this case, it made sense to have a user for the SQL Server, and that user could access multiple databases in the SQL Server.
Background of the new method
However, as I wrote in the article below, in Azure SQL Database, each database runs independently on a separate server, and it is not good at joining data between databases, as is the case with traditional SQL Server.
Therefore, there is little advantage in allowing one user to access multiple databases as a traditional user, but rather the risk is greater if all database users are dependent on a single master database.
Because of these differences between Azure SQL Database and SQL Server, Azure SQL Database recommends creating a user for each database and logging in directly to the database with that user, rather than the traditional SQL Server method of adding users. Azure SQL Database is recommended to create a user for each database and log in to the database directly with that user.
Procedure (new method)
The new method is not particularly difficult.
First, it eliminates the need to create a "login".
And there is no longer any need to tie a "login" to the "user" when creating a "user".
Instead, a "user" also serves as a conventional "login," so a password must be set when the "user" is created.
Based on the above, the new method of creating a "user and login" is as follows
CREATE USER <user_name> WITH PASSWORD='<password>'
The method of deleting users and assigning roles is the same as before.
The password will be changed as follows
ALTER USER <user_name> WITH PASSWORD='<password>'
Login to Azure SQL Database is done with the "user" and "password" you have created.
However, since "users" are defined in the database, the destination database must be specified when logging in.
Impressions, etc.
Roles can be assigned to multiple users, so if you want to add a user who can read and write, you can do so by granting "db_datareader" and "db_datawriter".
I see that Microsoft is trying various things to make SQL Server a managed service in line with the times.
Reference Articles
- https://blog.engineer-memo.com/2019/04/23/sql-server-%E3%81%AE%E3%83%AD%E3%82%B0%E3%82%A4%E3%83%B3-%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E3%81%AE%E6%A8%A9%E9%99%90%E3%82%92-ssms-%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%AA%E3%81%8C%E3%82%89/
- https://docs.microsoft.com/ja-jp/sql/relational-databases/security/contained-database-users-making-your-database-portable?redirectedfrom=MSDN&view=sql-server-2017
- https://docs.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-2017
