Server| programming Everyone is concerned about data security. If you don't care, you may not realize that it's easy to access sensitive data from the server. Because SQL Server has already considered this for us, we can use SQL Server's security policy directly when we develop the application.
In SQL Server, we can create a login account in Enterprise Manager and give certain permissions if we want to do it in our application?
SQL Server provides the following system procedures
One, sp_addlogin add login account
Sp_addlogin Login_id[,password[,defaultdb[,defaultlanguage]]]
LOGIN_ID account Name
Password Password
Defaultdb default database, default is master database.
DefaultLanguage Default language
Second, sp_adduser add users
Sp_adduser Login_id[,username[,rolename]]
Username the login Database alias
RoleName User-Subordinate group name
Third, sp_droplogin Delete account
Sp_droplogin login_id
Four, sp_dropuser Delete user
Sp_dropuser username
Grant grants user or group permission
GRANT permission_list on object_name to Name_list
List of permissions granted by Permission_list
object_name a table, view, or stored procedure that is granted permissions
Name_list a list of users or groups that are granted permissions
Vi. REVOKE Reclaim user or group permissions
REVOKE permission_list on object_name from Name_list
Seven, sp_password change password
sp_password [old_password,]new_password[,login_id]
The first six procedures can only be used by an SA or a person who is granted security administrators permission by the SA, and the seventh procedure may be used by ordinary users but cannot use login_id items, which are available only to those with the above permission
application Example:
Add to
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
This code is tested in NT4+SQL7 and WIN98+SQL7 and nt4+sql6.5 through
References: SQL Server 7.0 Development Guide
Author: Water
