Several system stored procedures used by SQL server login, logon, users, roles, and groups

Logon, users, roles, and groups are the foundation of Microsoft SQL Server 2000 security mechanism. Users connected to SQL Server must use a specific logon ID to identify themselves. Therefore, you can only view tables and views that are authorized to view, and can only execute stored procedures and management functions that can be authorized. This security system is based on the ID used to identify the user.

Sp_addlogin

Create a new SQL Server login so that you can connect to an SQL Server instance that uses SQL Server Authentication

Sp_addrole

Create a new SQL Server role in the current database

-- What is a role ???

For example, a company may set up a charity committee, including employees from different departments and different levels in the Organization. These employees need to access Special Project tables in the database. There are no existing Windows NT 4.0 or Windows 2000 groups that only include these employees, and there is no other reason to create such a group in Windows NT 4.0 or Windows 2000. You can create a custom SQL Server database role CharityEvent for this project and add some Windows NT and Windows 2000 users to this database role. After the application permission is granted, the user in the database role can access the table. Permissions for other database activities are not affected. Only CharityEvent users can use this project table.

 

The SQL Server role exists in one database and cannot span multiple databases.

Benefits of using database roles include:

1. for any user, multiple database roles can be active at any time. if all users, groups, and roles are in the current database, the SQL Server role can include Windows NT 4.0 or Windows 2000 Groups and users, as well as SQL Server users and other roles.

2. In the same database, a user can belong to multiple roles.

3. A scalable model is provided to set the correct security level in the database.

Note: A database role is owned by a user explicitly specified as the owner when creating a role, or by a user who creates a role when no owner is specified. The owner of the role determines who can add or delete the role. However, because the role is not a database object, you cannot create multiple roles with the same name owned by different users in the same database.

 

Sp_addrolemember

Add a security account as a member of an existing Microsoft SQL Server database role in the current database.

Sp_adduser

Add a security account to a new user in the current database.

Sp_defaultdb

Change the default database for login

Sp_defaultlanguage

Change the default logon Language

sp_droplogin

Delete Microsoft SQL Server login to prevent the use of this login name to access SQL Server

Sp_grantlogin

Enables Microsoft Windows NT users or group accounts to connect to Microsoft SQL Server using Windows authentication.

Sp_helpuser

Reports information about Microsoft SQL Server users, Microsoft Windows NT users, and database roles in the current database

Sp_password

Add or change the Microsoft SQL Server logon Password

Sp_revokelogin

Delete a user from Microsoft SQL ServerSp_grantloginOrSp_denyloginThe logon entry of the created Microsoft Windows NT user or group.

xp_logininfo

Report account, Account type, account privilege level, account ing login name, and account access to Microsoft SQL Server

Ps: The text is from the help of SQL server Chinese.