SQL Server database object access control

The following content is primarily for database-level data access (such as SELECT, INSERT, UPDATE, Delete, execute ...). ）

1. Direct to user permissions
GRANT EXECUTE to [user]

2. Through role control permissions, add user to role and inherit permissions owned by role
GRANT EXECUTE to [Role]
ALTER role [Role] ADD MEMBER [MEMBER]

3. Accessing the database via app role
Application role is a database-level role that does not contain any user, cannot be used directly in the database, and can only be used by the application to connect to the database.

Application role cannot be in the same time as the previous two ways, if a login already has a database corresponding to the user, and has given the appropriate permissions, but after the login to activate the application Role, then the login security The context switches to the security context of application role, which means that only application role permissions work.

It is important to note that after activating the application role in the application, to access other databases can only be used with the Guest account, since the guest is considered a thorn in the Thorn by many DBAs, think twice if you want to use application role.

4.Sp EXECUTE AS XX
This is a more recommended way to control permissions on a minimum granularity.
This method is like the Imperial sword, such as a is SP1 OWNER,SP1 defined as the execute as owner, and then B has the SP1 execution permission, then B executes the SP1 when it has all the rights of a, valid until the end of SP1 execution.

Similar usages include EXECUTE as caller/self/"username"/"LoginName". By default, if the SP does not write the EXECUTE AS statement, it is using EXECUTE as caller.

5.Owned schema
Schema as a collection of objects in the database, can also be used to control permissions, by giving the user permissions to the schema, it also gives the schema contains all the TABLE,VIEW,SP permissions.
Can be given the schema permission, and then at the object level (Table,view ...) Modify access permissions, and ultimately take precedence over object-level permissions.

SQL Server database object access control