Requirements: The website uses SQL Server as persistent storage, and needs to configure the database address, account, password, and connection string configurationSA users, the privileges are a bit too muchI want to create a new user to only allow all permissions to a certain database.
This article uses the SQL Server Management Studio tool to connect to the SQL Server 2019 database service as an example.
Open SQL Server Management Studio and log in with Windows authentication (or use your SA account).
First, create a new TestDB database with the following command:
Click the left menu [Security] --- [Login], then right-click "Login", and click [New Login], as shown below:
In the General tab, fill in the login name, password, confirm password, and select the default database, as shown in the following figure:
Switch to the user map, check the target database [TestDB], and grant public, db_owner permissions (so that the new user itsvse can have administrator permissions on the database TestDB), as shown in the figure below:
Finally, click OK.
We try to use a newly created user to log in to the database, as shown in the following figure:
Although you can see other databases, you will get the following error when you try to access them:
Title: Microsoft SQL Server Management Studio
------------------------------
The database Test is not accessible. (ObjectExplorer)
------------------------------
Button:
Are you sure
------------------------------
Finally, attach a description of permissions:
'db_owner' - has full database permissions, including delete database permissions
'db_accessadmin' - only gives the database user permission to create other database users, but not the permission to create logged-in users.
'db_securityadmin' - Manage full permissions, object ownership, roles, and role memberships
'db_ddladmin' - can emit all DDLs (Create, Alter and Drop), but not GRANT, REVOKE or DENY statements
'db_backupoperator' -- Permission to allow database backup and restore [backup and restore can also be done through SQL Sever Management Studio]
'db_datareader' - can select all data from any user table in the database
'db_datawriter' - can change all data in any user table within the database
'db_denydatareader' - cannot query any data in any user table within the database
'db_denydatawriter' - cannot change any data in any user table in the database
(End)
|
Posted on 5/22/2022 12:26:37 PM
|
|
|
|
Posted on 5/22/2022 3:21:07 PM
|
Landlord