Shulou(Shulou.com)06/01 Report--

In database development, it is often necessary to set the user's permission to operate the database. There are usually two ways:

1. In the database, establish a permission table to record the operation rights of each user to the corresponding module (interface), and then use it in the program.

Some of the companies I've seen develop databases in this way (which is also the case with Yuyou).

2. Establish database users directly in SQL SERVER, and configure the operation rights of each user to the data table.

Here I mainly talk about the second way, the possible use of the SQL statement, directly configure the permissions of the data table, and then return the user's permissions on the table through the SQL statement, which can be used in the application.

Select * from sys.sysusers where name= [name]-- View the information of database users

Select * from sys.syslogins where name= [name]-- View the information of the logged in user

Create login [loginName] with password='password'-- create a login and set the password

Alter login [old_loginName] with name= [new _ name]-- modify the login name

Alter login [loginName] with password=' [new password]'--change the password

Drop login [loginName]-- Delete login

Create user [db user] for login [login name]-create a user (login must be specified)

Alter user [old db user] with name= [new db user]-- modify the user

Sp_addrole [role_name]-- add database roles

Deny connect sql to [db user]-whether to allow users to connect to the database engine grant

Alter login [login name] enable | disable-allow or disable login

Deny | grant connect to [db user]-prohibit or authorize users to access the database

Alter role [old_role_name] with name= [new _ role_name]-- change the role name

Query whether the login has permission to connect to the database engine

SELECT state_desc from sys.server_permissions perm join sys.server_principals pri

On perm.grantee_principal_id=pri.principal_id where pri.name=' [login name]'

Query whether the logged in user is prohibited or started

Select is_disabled from sys.server_principals

Where name=' [login name]'

Query the permissions of users to connect to the database

Select permission_name from sys.database_permissions

Where class=0 and grantee_principal_id=DATABASE_PRINCIPAL_ID ('[db user]')

Query the user's permissions on the table

Select sysp.type collate database_default,permission_name,state_desc,obj.name

From sys.database_permissions sysp

Join sys.all_objects obj on obj.object_id=sysp.major_id

Where sysp.grantee_principal_id=database_principal_id ('db user') and obj.name=' [table name]'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.