Now it is necessary to restrict specific users to view and access specific databases, so as to prevent some misoperations when multiple users operate on databases.
Refer to i6first’s blog on how to let users only access a specific database (MSSQL)
1. Create a new login user
Log in to the database as an administrator (the highest authority, such as SA), click Security > login name, right-click to create a new login name, enter the login name and password, and cancel the enforcement of password policy.
2. Set the server role to public
3. Remove the permission to view all databases from the public server role properties
Click Security > server role > public, and right-click properties. Click the server as shown in the figure below to cancel the permission to view any database
Or execute the following SQL:
REVOKE VIEW ANY DATABASE TO [public]
4. DB of the database to be authorized_ The owner is assigned to the new user who has just registered
Create a new query in the database and enter the following SQL statement. Complete the setting of user rights, so that new users can only view and operate specific databases (except system databases)
Use [WH_ R]//WH_ R is the database name go EXEC dbo.sp_ Changedbown n'user '// user is the newly created user