Mssql server2005 specifies a separate user name for the database

-- Create role r_test

The code is as follows:	Copy code
EXEC sp_addrole 'r _ test'

-- Grant r_test all permissions on the jobs table

The code is as follows:	Copy code
Grant all on jobs TO r_test

-- Grant the role r_test the SELECT permission on the titles table.
Grant select on titles TO r_test

-- Add users that only allow access to the specified table:

The code is as follows:	Copy code
Exec sp_addlogin 'username', 'password', 'default database name'

-- Add to database

The code is as follows:	Copy code
Exec sp_grantdbaccess 'username'

-- Assign full table permissions

The code is as follows:	Copy code
Grant select, INSERT, UPDATE, delete on table1 TO [user name]

The preceding SQL statements can be run directly under the sa user. The following figure shows how convenient the SQL statements are.

 

1.       Log on to SQL Server 2005, "security"-"login name"-"new login name"

(1)     General

Login name (custom)

SQL Server Authentication

Note: Do not use "force password expiration". Otherwise, you cannot create a new user.

(2)     User ing

The user mapped to this login name, select the database you want to log on.

Note: log on to the database using the login name.

You can set the user's access permissions (read, write, and so on ).

SQL Server 2005 supports Setting column access permissions.

(3)     Status

Grant the permission to link to the database engine; enable logon.

2.       After refreshing, when we look at the Database again, a user is added under "security"-"user.

3.       After starting SQL Server 2005, you can use this user to log on to the database.