这期内容当中小编将会给大家带来有关怎么在SQL Server中处理数据库角色权限，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

1：遍历所有用户数据库（排除了系统数据库以及一些特殊数据库），发现该数据库不存在这些通用数据库角色，那么就创建相关数据库角色。

2：遍历所有用户数据库，为相关数据库角色授权，例如，如果发现某个新增的存储过程，没有授权给db_procedure_execute数据库角色。那么就执行授权操作。

当然目前还在测试、应用阶段，以后会根据具体相关需求，不断完善相关功能。

USEYourSQLDba;GOIFEXISTS(SELECT1FROMsys.proceduresWHEREtype='P'ANDname='db_common_role_grant_rigths')BEGINDROPPROCEDUREMaint.db_common_role_grant_rigths;ENDGOCREATEPROCEDUREMaint.db_common_role_grant_rigthsASBEGINDECLARE@database_idINT;DECLARE@database_namesysname;DECLARE@cmdTextNVARCHAR(MAX);DECLARE@prc_textNVARCHAR(MAX);DECLARE@RowIndexINT;IFOBJECT_ID('TempDB.dbo.#databases')ISNOTNULLDROPTABLEdbo.#databases;CREATETABLE#databases(database_idINT,database_namesysname)IFOBJECT_ID('TempDB.dbo.#sql_text')ISNOTNULLDROPTABLEdbo.#sql_text;CREATETABLE#sql_text(sql_idINTIDENTITY(1,1),sql_cmdNVARCHAR(MAX))INSERTINTO#databasesSELECTdatabase_id,nameFROMsys.databasesWHEREnameNOTIN('master','tempdb','model','msdb','distribution','ReportServer','ReportServerTempDB','YourSQLDba')ANDstate=0;--state_desc=ONLINE--开始循环每一个用户数据库（排除了上面相关数据库）WHILE1=1BEGINSELECTTOP1@database_name=database_nameFROM#databasesORDERBYdatabase_id;IF@@ROWCOUNT=0BREAK;--PRINT(@database_name);--SP_EXECUTESQL中切换数据库不能当参数传入。--创建数据库角色db_procedure_executeSET@cmdText='USE'+@database_name+';'+CHAR(10)SELECT@cmdText+='IFNOTEXISTS(SELECT1FROMsys.database_principalsWHEREname=''db_procedure_execute'')BEGINCREATEROLE[db_procedure_execute]AUTHORIZATION[dbo];END'+CHAR(10);--创建数据库角色db_function_executeSELECT@cmdText+='IFNOTEXISTS(SELECT1FROMsys.database_principalsWHEREname=''db_function_execute'')BEGINCREATEROLE[db_function_execute]AUTHORIZATION[dbo];END'+CHAR(10);--创建数据库角色db_view_table_definitionSELECT@cmdText+='IFNOTEXISTS(SELECT1FROMsys.database_principalsWHEREname=''db_view_table_definition'')BEGINCREATEROLE[db_view_table_definition]AUTHORIZATION[dbo];END'+CHAR(10);--创建数据库角色db_view_view_definitionSELECT@cmdText+='IFNOTEXISTS(SELECT1FROMsys.database_principalsWHEREname=''db_view_view_definition'')BEGINCREATEROLE[db_view_view_definition]AUTHORIZATION[dbo];END'+CHAR(10);--创建数据库角色db_view_procedure_definitionSELECT@cmdText+='IFNOTEXISTS(SELECT1FROMsys.database_principalsWHEREname=''db_view_procedure_definition'')BEGINCREATEROLE[db_view_procedure_definition]AUTHORIZATION[dbo];END'+CHAR(10);--创建数据库角色db_view_function_definitionSELECT@cmdText+='IFNOTEXISTS(SELECT1FROMsys.database_principalsWHEREname=''db_view_function_definition'')BEGINCREATEROLE[db_view_function_definition]AUTHORIZATION[dbo];END'+CHAR(10);--PRINT@cmdText;--EXECUTESP_EXECUTESQL@cmdText;EXECUTE(@cmdText);--给角色db_procedure_execute授权SET@cmdText='USE'+QUOTENAME(@database_name)+';'SET@cmdText+='INSERTINTO#sql_text(sql_cmd)SELECT''GRANTEXECUTEON''+SCHEMA_NAME(schema_id)+''.''+QUOTENAME(name)+''TOdb_procedure_execute;''FROMsys.proceduressWHERENOTEXISTS(SELECT1FROMsys.database_permissionspWHEREp.major_id=s.object_idANDp.grantee_principal_id=USER_ID(''db_procedure_execute''))';EXECUTESP_EXECUTESQL@cmdText;--给角色db_function_execute(标量函数授权)SET@cmdText='USE'+QUOTENAME(@database_name)+';'SET@cmdText+='INSERTINTO#sql_text(sql_cmd)SELECT''GRANTEXECON''+SCHEMA_NAME(schema_id)+''.''+QUOTENAME(name)+''TOdb_function_execute;''FROMsys.all_objectssWHERESCHEMA_NAME(schema_id)NOTIN(''sys'',''INFORMATION_SCHEMA'')ANDNOTEXISTS(SELECT1FROMsys.database_permissionspWHEREp.major_id=s.object_idANDp.grantee_principal_id=USER_ID(''db_function_execute''))AND(s.[type]=''FN''ORs.[type]=''AF''ORs.[type]=''FS''--ORs.[type]=''FT'');'EXECUTESP_EXECUTESQL@cmdText;--给角色db_function_execute(表值函数授权)SET@cmdText='USE'+@database_name+';'SET@cmdText+='INSERTINTO#sql_text(sql_cmd)SELECT''GRANTSELECTON''+SCHEMA_NAME(schema_id)+''.''+QUOTENAME(name)+''TOdb_function_execute;''FROMsys.all_objectssWHERESCHEMA_NAME(schema_id)NOTIN(''sys'',''INFORMATION_SCHEMA'')ANDNOTEXISTS(SELECT1FROMsys.database_permissionspWHEREp.major_id=s.object_idANDp.grantee_principal_id=USER_ID(''db_function_execute''))AND(s.[type]=''TF''ORs.[type]=''IF'');'EXECUTESP_EXECUTESQL@cmdText;--查看存储过程定义授权SET@cmdText='USE'+@database_name+';'SET@cmdText+='INSERTINTO#sql_text(sql_cmd)SELECT''GRANTVIEWDEFINITIONON''+SCHEMA_NAME(schema_id)+''.''+QUOTENAME(name)+''TOdb_view_procedure_definition;''FROMsys.proceduressWHERENOTEXISTS(SELECT1FROMsys.database_permissionspWHEREp.major_id=s.object_idANDp.grantee_principal_id=USER_ID(''db_view_procedure_definition''))'EXECUTE(@cmdText);--查看函数定义的授权SET@cmdText='USE'+@database_name+';'SELECT@cmdText+='INSERTINTO#sql_text(sql_cmd)SELECT''GRANTVIEWDEFINITIONON''+SCHEMA_NAME(schema_id)+''.''+QUOTENAME(name)+''TOdb_view_function_definition;''FROMsys.objectssWHEREtype_descIN(''SQL_SCALAR_FUNCTION'',''SQL_TABLE_VALUED_FUNCTION'',''AGGREGATE_FUNCTION'')ANDNOTEXISTS(SELECT1FROMsys.database_permissionspWHEREp.major_id=s.object_idANDp.grantee_principal_id=USER_ID(''db_view_function_definition''))';EXECUTESP_EXECUTESQL@cmdText;--查看表定义的授权SET@cmdText='USE'+@database_name+';'SET@cmdText+='INSERTINTO#sql_text(sql_cmd)SELECT''GRANTVIEWDEFINITIONON''+SCHEMA_NAME(schema_id)+''.''+QUOTENAME(name)+''TOdb_view_table_definition;''FROMsys.tablessWHERENOTEXISTS(SELECT1FROMsys.database_permissionspWHEREp.major_id=s.object_idANDp.grantee_principal_id=USER_ID(''db_view_table_definition''))';EXECUTESP_EXECUTESQL@cmdText;--查看视图定义的授权SET@cmdText='USE'+@database_name+';'SET@cmdText+='INSERTINTO#sql_text(sql_cmd)SELECT''GRANTVIEWDEFINITIONON''+SCHEMA_NAME(schema_id)+''.''+QUOTENAME(name)+''TOdb_view_view_definition;''FROMsys.viewssWHERENOTEXISTS(SELECT1FROMsys.database_permissionspWHEREp.major_id=s.object_idANDp.grantee_principal_id=USER_ID(''db_view_view_definition''))';EXECUTESP_EXECUTESQL@cmdText;WHILE1=1BEGINSELECTTOP1@RowIndex=sql_id,@cmdText='USE'+@database_name+';'+sql_cmdFROM#sql_textORDERBYsql_id;IF@@ROWCOUNT=0BREAK;PRINT(@cmdText);EXECUTE(@cmdText);DELETEFROM#sql_textWHEREsql_id=@RowIndexENDDELETEFROM#databasesWHEREdatabase_name=@database_name;ENDDROPTABLE#databases;DROPTABLE#sql_text;END

上述就是小编为大家分享的怎么在SQL Server中处理数据库角色权限了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。