SQL Server database permissions

In database development, it is often necessary to set user permissions on the database, usually the following 2 ways:

1, in the database, establish a permission table, record each user to the corresponding module (interface) operation permissions, and then used in the program.

I have seen some of the company's database development (and so is the implementation of UF) in this way.

2. Establish database users directly in SQL Server and configure each user's permissions on the data table.

Here I mainly describe the 2nd way, may use the SQL statement, directly configure the data table permissions, and then through the SQL statement to return the user to the table operation permissions, can be used in the application.

SELECT * from Sys.sysusers where name=[name]--View information for database users

SELECT * from Sys.syslogins where name=[name]--View information for logged-in users

Create login [LoginName] with password= ' password '--Creates a login and sets a password

ALTER LOGIN [Old_loginname] with Name=[new_name]--Modify login name

ALTER LOGIN [LoginName] with password= ' [New password] '--Change password

Drop login [LoginName]--Delete login name

Create user [db user] for login [login name]--Creating users (must specify login name)

alter user [old DB user] with Name=[new DB user]--Modify users

sp_addrole [Role_name]--Adding database roles

Deny connect SQL to [DB User]--whether the user is allowed to connect to the database engine grant

ALTER LOGIN [login name] enable|disable--Allow or disallow login name

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

alter role [Old_role_name] with Name=[new_role_name]--Change character name

Querying logins for permissions 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] '

Querying whether the logged-on user is forbidden or started

Select is_disabled from Sys.server_principals

where name= ' [login name] '

To query whether a user can connect to a database

Select Permission_name from Sys.database_permissions

where Class=0 and grantee_principal_id=database_principal_id (' [db user] ')

Querying user permissions on a 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] '

This article is from the "Love My Love" blog, please be sure to keep this source http://tommy.blog.51cto.com/113624/1876656

SQL Server database permissions