SqlServer server role and database role-related operations, sqlserver Database

SqlServer server role and database role-related operations, sqlserver Database

/* Role [server-server role] roles */-- view the fixed server roles (8, not added or deleted, not public, and each user belongs to the public server role) EXEC sp_helpsrvroleEXEC sp_helpsrvrole @ srvrolename = 'sysadmin' -- view the permissions of a server role (server role details !) EXEC sp_srvrolepermissionEXEC sp_srvrolepermission @ srvrolename = 'sysadmin' -- determine whether a Logon account has a server role (If yes, it is 1; otherwise, it is 0) SELECT IS_SRVROLEMEMBER ('sysadmin ') SELECT IS_SRVROLEMEMBER ('sysadmin', 'sa ') -- the Server role can be created in SQL Server 2012! ~ Create server role [ServerRoleKK] -- add the login name as a member of a SERVER-level role exec sp_addsrvrolemember @ loginame = 'kk ', @ rolename = 'sysadmin' -- deletes the SQL Server logon name or Windows user or group EXEC sp_dropsrvrolemember @ loginame = 'kk' from a Server-level role ', @ rolename = 'sysadmin' -- View server role and member relationship EXEC sp_helpsrvrolememberEXEC sp_helpsrvrolemember @ srvrolename = 'sysadmin' -- View server role and member relationship (more details) SELECT rsp. principal_id as [role_principal_id], rsp. name AS [Server_Role], sp. principal_id, sp. name, sp. [sid], case when sp. [type] ='s 'then' SQL login name 'when sp. [type] = 'u'then' Windows login name 'when sp. [type] = 'G' then' Windows Group 'when sp. [type] = 'r'then' server role 'when sp. [type] = 'C' then' maps to the certificate login name 'when sp. [type] = 'K' then' maps to the login name of the asymmetric key 'end AS [Principal_Type], sp. create_date, sp. modify_date, sp. is_disabledFROM sys. server_principals sp inner join sys. server_role_members srm ON sp. principal_id = srm. member_principal_id inner join sys. server_principals rsp ON srm. role_principal_id = rsp. principal_id -- View Server Object permission control status SELECT sp1.principal _ id AS grantor_principal_id, sp1.name AS grantor_name, sp1.type _ desc AS Alias, sp2.principal _ id AS grantee_principal_id, sp2.name AS grantee_name, sp2.type _ desc AS grantee_type_desc, spe. class_desc, spe. state_desc, spe. permission_nameFROM sys. server_principals sp1INNER JOIN sys. server_permissions spe ON sp1.principal _ id = spe. grantor_principal_idINNER JOIN sys. server_principals sp2 ON sp2.principal _ id = spe. grantee_principal_id -- change server-level permission control: Security --> login name -- Right-click Logon account --> properties --> Security object --> Search (with server/Endpoint/login name) --> lower control permissions

/* Revoke [database level-database role] role */-- view the fixed database role (excluding the public role. Each database user belongs to the public database role) EXEC sp_helpdbfixedroleEXEC sp_helpdbfixedrole @ rolename = 'db _ owner' -- view the permissions of the database role (detailed permission description for the database role !) EXEC sp_dbfixedrolepermissionEXEC sp_dbfixedrolepermission @ rolename = 'db _ owner' -- Information about roles in the current database (at least 10 fixed database roles) EXEC sp_helproleEXEC sp_helprole @ rolename = 'db _ owner' -- IsAppRole: application role -- information about members of a role EXEC sp_helprolememberEXEC sp_helprolemember @ rolename = 'db _ owner' -- whether the current user is a member of a specified Microsoft Windows Group or SQL Server database role SELECT IS_MEMBER ('db _ owner ') -- create a database role (for example, create a role Myrole and have the db_owner role) create role MyRole AUTHORIZATION db_owner -- Grant ROLE permissions (Database roles are granted to database users, database roles, Windows logon names, or Windows groups in the current database) EXEC sp_addrolemember @ rolename = 'myrole ', @ membername = 'myuser' -- revoke role permissions (the database role is reclaimed in the current database, the opposite is true) EXEC sp_addrolemember @ rolename = 'myrole ', @ membername = 'myuser' -- change the role name alter role MyRole with name = NewRole -- delete the role drop role NewRole -- SELECT u for the current database ROLE and user relationship. name as UserName, u. type_desc, u. default_schema_name, g. name as DBRoleFROM sys. database_principals uinner join sys. database_role_members m on u. principal_id = m. member_principal_idinner join sys. database_principals g on g. principal_id = m. role_principal_idORDER BY UserName, DBRole -- Change Database-level permission control: a database --> Security -- role --> database role --> right-click role --> you can add members on your own.

/* Role [database-application role] role */Application role is a database subject, you can only use the permissions granted to guest in other databases to access these databases. Application roles in other databases will not be able to access any databases with disabled guest. users connected to specific applications can only access specific databases. what is different from the database role is that, by default, an application role does not contain any members and is not active. To enable an application role, a password is required. The application role cannot access server-level metadata. If access is allowed, you need to enable tracking flag 4616 (dbcc traceon (4616,-1) -- create application role [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N 'apache' -- change the application role name, PASSWORD, or default architecture. alter application role [MyAppRole] with name = [AppRole], PASSWORD = 'apache ', DEFAULT_SCHEMA = [dbo] -- activate permissions associated with the application role in the current database (cannot be executed in other stored procedures or user-defined transactions) DECLARE @ cookie varbinary (8000) EXEC sp_setapprole @ rolename = 'apache', @ password = 'apache' --, @ password = {encrypt N 'apache'} -- ODBC encrypt function encryption, @ encrypt = 'none' -- 'none' | 'odbc ': whether to perform fuzzy encryption. @ fCreateCookie = true -- true | false: whether to create a cookie, @ cookie = @ cookie OUTPUT -- Obtain the security context before the application role SELECT @ cookie -- remember cookie -- view the current login user (found to be [AppRole]) SELECT CURRENT_USER, USER_NAME () -- disable the application role and restore it to the previous security context (cookie created for sp_setapprole) EXEC sp_unsetapprole @ cookie = ROLE -- delete the application role drop application role [AppRole] -- view the application role select * FROM sys. sysusers WHERE isapprole = 1