SQL Server role and database role related actions

/*------------------------------------------------------------------------------------Server-level-server role----------- -------------------------------------------------------------------------*/--View fixed server roles (8, no additions or deletions, not including public, Each user belongs to the public server role) EXEC sp_helpsrvroleexec sp_helpsrvrole @srvrolename = ' sysadmin '--View the description of the permissions that the server role has (the server role detailed permission description!). EXEC sp_srvrolepermissionexec sp_srvrolepermission @srvrolename = ' sysadmin '--determine if a login account has a server role (or 1, otherwise 0) SELECT Is_srvrolemember (' sysadmin ') SELECT is_srvrolemember (' sysadmin ', ' sa ')--server roles can create!~create server in SQL Server 2012 Role [serverrolekk]--adds the login as a member of a server-level role EXEC sp_addsrvrolemember @loginame = ' kk ', @rolename = ' sysadmin '--removed from the server-level role SQL Server logon name or Windows user or group exec sp_dropsrvrolemember @loginame = ' kk ', @rolename = ' sysadmin '--View server roles and memberships exec sp_he Lpsrvrolememberexec sp_helpsrvrolemember @srvrolename = ' sysadmin '--View server roles and memberships (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]= ' Then ' SQL login ' when sp.[type]= ' U ' then ' Windows login ' when sp.[type]= ' G ' then ' Windows group ' when sp.[type]= ' R ' T HEN ' server role ' when sp.[type]= ' C ' and ' when ' is mapped to the login name of the certificate ' when sp.[type]= ' K ' then ' is mapped 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--viewing Server object permissions control select Sp1.principal_id as grantor_principal_id,sp1.name as grantor_name,sp1.type_desc as Grantor_ type_desc,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--for server-level permissions control changes: Security--Login name--right-click on the login-----------with the server/endpoint/login name)--control permissions below 

/*------------------------------------------------------------------------------------Database-level-database role----------- -------------------------------------------------------------------------*/--View fixed database roles (excluding the public role, each database user belongs to Public database role) EXEC sp_helpdbfixedroleexec sp_helpdbfixedrole @rolename = ' db_owner '--view a description of the permissions that the database role has (detailed permission description for database roles!) 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: Whether the application role--information about the members of a role exec sp_helprolememberexec sp_ Helprolemember @rolename = ' db_owner '--whether the current user is a member of the specified Microsoft Windows group or SQL Server database role Select Is_member (' db_owner ')-- Create a database role (such as create role myrole and have the db_owner role) create role Myrole AUTHORIZATION db_owner--Grant roles permissions (the database role is granted to database users, database roles, Windows logins or Windows groups) EXEC sp_addrolemember @rolename = ' Myrole ', @membername = ' MyUser '--Reclaim role permissions (the database role is recycled in the current database, as in the opposite) EXEC sp_addrolemember @rolename = ' Myrole ', @membername = ' MyUser '--change role name alter roles Myrole withNAME = newrole--Remove Roles drop role newrole--current database role and user relationship select U.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_idi Nner join sys.database_principals g on g.principal_id = M.role_principal_idorder by username,dbrole--for database-level permission control changes: a database --security--role----the database role--right---add members to your own

/*------------------------------------------------------------------------------------Database level-application role----------- -------------------------------------------------------------------------*/Application role is a database principal and can only be used in other databases as guest Grant permissions to access these databases application roles in other databases will not be able to access any disabled guest database can only allow users connecting through a particular application to access specific data unlike database roles, the application role does not contain any members by default. And the inactive enable application role requires the password application role to be unable to access server-level metadata, if access is allowed, you need to turn on trace flag 4616 (DBCC TRACEON (4616,-1))--Create an application role created application role [  Myapprole] with default_schema = [dbo], PASSWORD = N ' approle '--Change the name, password, or default schema of the application role alter application role [Myapprole] with NAME = [Approle],password = ' AppRole ', Default_schema = [dbo]--Activates the permissions associated with application roles in the current database (cannot be performed in other stored procedures or user-defined transactions) DECLARE @ Cookie varbinary (8000) EXEC sp_setapprole @rolename = ' AppRole ', @password = ' approle '--, @password = {Encrypt N ' AppRole '}--odbc encrypt function encryption, @encrypt = ' None '--' none ' | ' ODBC ': Whether fuzzy encryption processing, @fCreateCookie = true--True|false: Whether to create a cookie, @cookie = @cookie output--Get the application role before the security context select @cookie- -Remember cookie--view current logged on user (found as [approle]) SeleCT Current_user,user_name ()--Deactivate the application role and revert to the previous security context (created by the cookie for sp_setapprole) EXEC sp_unsetapprole @cookie =  0x5e76db4ef3e20388c5cbde4feb63dc409baad344d11919ef23af18743a8b40bbeb3f972e3d84c5f7fbe2c1a853934ebc0100--Remove application Role drop application role [approle]--View with program role SELECT * FROM sys.sysusers WHERE isapprole = 1

SQL Server role and database role related actions