SQL Server new read-only permission user

1, create a new read-only user that can access only one table.

--Add users who only allow access to the specified table:
Execsp_addlogin ' username ', ' password ', ' Default database name '

--Add to Database
Execsp_grantdbaccess ' User name '

--Assigning Select Full table permissions
Grantselecton table name to[user Name]

--Assigning SELECT permissions to a specific column
Grantselecton table name (ID,AA) to[user name]

For example:

--Add users who only allow access to the specified table:
Execsp_addlogin ' read ', ' 123456 ', ' Test '

Execsp_grantdbaccess ' read '

--Assigning Select Full table permissions
Grantselectondbo.usercardsto[read]

2, create a new read-only user that accesses the entire database

--Add users who only allow access to the specified table:
Execsp_addlogin ' username ', ' password ', ' Default database name '

--Add to Database
Execsp_grantdbaccess ' User name '

--Add to role
Execsp_addrolemember ' db_datareader ', ' User name '

3, fixed server role

Sysadmin
You can perform any activity in SQL Server.

ServerAdmin
You can set server-wide configuration options to shut down the server.

Setupadmin
You can manage linked servers and startup procedures.

Securityadmin
You can manage logins and CreateDatabase permissions, as well as read error logs and change passwords.

Processadmin
You can manage processes that run in SQL Server.

DBCreator
You can create, change, and drop databases.

Diskadmin
You can manage disk files.

Bulkadmin
The Bulkinsert statement can be executed.

4, (User mapping) database role membership

db_owner performs all maintenance and configuration activities in the database.
db_accessadmin Add or Remove access permissions for Windows users, groups, and SQL Server logins.
Db_datareader reads all the data from all user tables.
db_datawriter Add, remove, or change data from all user tables.
db_ddladmin runs any data definition language (DDL) commands in the database.
db_securityadmin Modify role memberships and manage permissions.
Db_backupoperator back up the database.
Db_denydatareader cannot read any data from the database user table.
Db_denydatawriter cannot add, modify, or delete data from any user table or view.

What if you want to assign read-only permissions to SQL Server tables? Let's teach you the read-only permission assignment method for SQL Server tables, and you want to be enlightened about your ability to learn SQL Server table permissions.

2. --Create a test library first test_tmp
6. --Create two tables based on an existing table
10. SELECT TOP to test_tab1 from Mistest_09428.dbo.squarer;
14. SELECT TOP to test_tab2 from Mistest_09428.dbo.squarer;
18. --Create a role
20. EXEC sp_addrole ' Test_role1 ';
24. --Assign the permissions of select to the role test_role1
26. GRANT SELECT on TEST_TAB1 to Test_role1;
28. GRANT SELECT on test_tab2 to Test_role1;
32. --add login dap570, password dap570, default library is test_tmp
34. EXEC sp_addlogin ' dap570 ', ' dap570 ', ' test_tmp ';
38. --Add account test to dap570
40. EXEC sp_grantdbaccess ' dap570 ', ' Test '
44. --Assigning the role test_role1 to text
46. EXEC sp_addrolemember ' test_role1 ', ' test ';
48. --over
52. --Change dap570 to log in to the database test_tmp
56. --Then test
60. /*
62. DELETE from TEST_TAB1
66. INSERT into Test_tab1 (ingotno) VALUES (' 2222 ')
70. UPDATE test_tab1 SET ingotno = ' 3333 '
72. */

SQL Server new read-only permission user