Manage SQL SERVER accounts by programming

Everyone cares about data security. If you don't care about it, you may not realize that it is very easy to access sensitive data on the server. Because SQL SERVER has already considered this for us,
Therefore, we can directly use SQL SERVER security policies when developing applications.

In SQL SERVER, we can create a Logon account in ENTERPRISE MANAGER and grant certain permissions,
What if we want to do it in our application?

SQL SERVER provides the following system processes:

1. SP_ADDLOGIN: Add a Logon account

SP_ADDLOGIN login_id [, password [, defaultdb [, defaultlanguage>

Login_id account name
Password
Defaultdb default database, which is the MASTER database by default.
Defaultlanguage default language

2. Add a user to SP_ADDUSER

SP_ADDUSER login_id [, username [, rolename}

Username: the alias used to log on to the database.
Name of the group to which the rolename user belongs

Iii. SP_DROPLOGIN

SP_DROPLOGIN login_id

4. SP_DROPUSER delete a user

SP_DROPUSER username

5. GRANT user or group permission

GRANT permission_list ON object_name TO name_list

Permission_list list of permissions granted
Object_name: Table, view, or stored procedure authorized
Name_list list of users or groups granted Permissions

6. REVOKE revokes user or group permission

REVOKE permission_list ON object_name FROM name_list

7. change the password of SP_PASSWORD

SP_PASSWORD [old_password,] new_password [, login_id]

The first six processes can only be used by SA or persons authorized by SA to the Security Administrators permission. The seventh process may be used by common users, but the login_id item cannot be used. Only those with the preceding permissions can use this item.

　

Application instance:

Add

Declare @ login varchar (6), @ pass varchar (11)

Exec sp_addlogin @ login, @ pass, databasename
Exec sp_adduser @ login, @ login, public

Grant insert, select, update, delete on table1 to public

Delete

Revoke insert, delete on table1 from public

Exec sp_dropuser @ login
Exec sp_droplogin @ login