MSSQL server user permission settings

SQL server user access permission settings:

/* -- Example

Create a role r_test with all permissions of table jobs and SELECT permissions of table titles in database pubs, and then create a login Rochelle test, then, in the database pubs, the user account u_test is created for login l_test, and the user account u_test is added to the role r_test, so that it obtains the same permissions as the role r_test through permission inheritance, finally, the SELECT permission for table titles in user account u_test is denied by using the DENY statement. After using l_test to log on to the SQL Server instance, it only has all permissions for table jobs.

-- */USE pubs --

Create role r_test: EXEC sp_addrole r_test --

GRANT r_test ALL permissions ON the jobs table: 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 login l_test, set password to pwd, default database to pubs: EXEC sp_addlogin l_test, pwd, pubs --

Add the security account u_test: EXEC sp_grantdbaccess l_test, u_test --

Add u_test as a member of role r_test: EXEC sp_addrolemember r_test, u_test --

----------------------------- Supplement ----------------

The above authorization is to authorize a single table, 2005 can authorize the SCHEMA level,

GRANT the security account u_test with the query permission under the SCHEMA interface: grant select on schema: interface TO u_test --

DENY the security account u_test. The permission for dbo is deny view definition on schema: dbo TO risk --

DENY the SELECT permission of the security account u_test ON the titles table: deny select on titles TO u_test /*--

After completing the preceding steps, you can log on with l_test to perform all operations on the jobs table, but you cannot query the titles table. Although the role r_test has the select permission on the titles table, however, the select permission for titles has been explicitly denied in the security account, so l_test has no select permission for the titles table. --*/--

Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Delete A security account from the database pubs: EXEC sp_revokedbaccess u_test --

Delete login l_test: EXEC sp_droplogin l_test --

Delete role r_test: EXEC sp_droprole r_test exec sp_dropsrvrolemember Naa, sysadmin go exec sp_addsrvrolemember Naa, securityadmin go --

To ensure SQL security management, we should first ensure user security and simplicity, and only allow SQL users to access SQL (to prevent users in the administrator group from accessing SQL)

1. Enterprise Manager-right-click SQL instance-properties-security-Authentication-select "SQL server and windows"-OK 2. Enterprise Manager-security-login-right-click sa-set password -- other users also set passwords. 3. delete users: BUILTINAdministrators Administrator-this user may not be able to log on to SQL as a windows user. 4. Set the password required to enter the Enterprise Manager: in the Enterprise Manager, right-click your server instance (the one with the green icon) -- edit SQL Server Registration Attributes -- select "use SQL Server Authentication" -- and select "Always prompt to enter the login name and password" -- OK -- after the above settings, your SQL Server is basically safe.

Second, change the default port to hide the Server to reduce the possibility of attacks on the SQL Server: -- start -- program -- Microsoft SQL Server -- Server network utility -- enable the Protocol "TCP/IP" -- attribute -- default port, enter a custom port, for example, 2433 -- check to hide the server

Manage SQL users to prevent access to the databases they are not supposed to access (the total control and details can also control their permissions on specific objects of a database) --- switch to the database to be controlled by your new user: use your database name go ---- Add User: exec sp_addlogin test ---- add Logon: exec sp_grantdbaccess Ntest ---- make it the legal user of the current database: exec sp_addrolemember Ndb_owner, Ntest ---- grant all permissions to your own database ----- in this way, you can only access your own database. And the database contains the guest user's public table go ---- delete test user: exec sp_revokedbaccess Ntest ---- remove the database access permission: exec sp_droplogin Ntest ---- Delete login: if it is created in the Enterprise Manager, enterprise Manager -- Security -- Right-click logon -- New logon General -- enter the username in the name -- select the authentication method based on your needs (if you are using windows authentication, first create a user in the user of the operating system) -- in the default settings, select the Database Name and server role to be accessed by the new user, select the name of the database to be accessed by the user you created in database access, and select "public", "db_ownew, the user created in this way is the same as the user created in the preceding statement.

The last step is to set specific access permissions for specific users. For more information, see the following simple example:

-- Add a user that only allows access to the specified table: exec sp_addlogin username, password, default database name ----- add to database: exec sp_grantdbaccess username ---- assign the whole table permission: grant select, INSERT, UPDATE, delete on table1 TO [user name] ----- assign permissions TO specific columns: grant select, update on table1 (id, AA) TO [user name]