Questo articolo presenta una semplice stored procedure che consente di autorizzare un utente/ruolo di database ad accedere in una specifica modalità (es. SELECT, DELETE, ALTER, ..) a tutti gli oggetti appartenenti ad uno specifico schema di database oppure a tutti gli schema esistenti nel database al momento dell’esecuzione della stored procedure.
@user_db_role sysname,@schema_name sysname = NULL,@command varchar(6) = ‘GRANT’, –GRANT or REVOKE or DENY@flag_control bit = 0,@flag_take_ownership bit = 0,@flag_view_change_tracking bit = 0,@flag_view_definition bit = 0,@flag_alter bit = 0,@flag_delete bit = 0,@flag_execute bit = 0,@flag_insert bit = 0,@flag_references bit = 0,@flag_select bit = 0,@flag_update bit = 0
for
‘guest’,‘INFORMATION_SCHEMA’,‘sys’,‘db_owner’,‘db_accessadmin’,‘db_securityadmin’,‘db_ddladmin’,‘db_backupoperator’,‘db_datareader’,‘db_datawriter’,‘db_denydatareader’,‘db_denydatawriter’,@user_db_role
(@schema_name is null or @schema_name = name);
print ‘– ‘ + @command + ‘ permissions on [‘ + @schema_name + ‘]’;if @flag_control = 1beginset @sql_string = @command + ‘ CONTROL ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_take_ownership = 1beginset @sql_string = @command + ‘ TAKE OWNERSHIP ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_view_change_tracking = 1beginset @sql_string = @command + ‘ VIEW CHANGE TRACKING ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_view_definition = 1beginset @sql_string = @command + ‘ VIEW DEFINITION ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_alter = 1beginset @sql_string = @command + ‘ ALTER ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_delete = 1beginset @sql_string = @command + ‘ DELETE ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_execute = 1beginset @sql_string = @command + ‘ EXECUTE ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_insert = 1beginset @sql_string = @command + ‘ INSERT ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_references = 1beginset @sql_string = @command + ‘ REFERENCES ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_select = 1beginset @sql_string = @command + ‘ SELECT ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_update = 1beginset @sql_string = @command + ‘ UPDATE ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;fetch next from c_schemas into @schema_name;
@user_db_role = ‘AW_Role’,@schema_name = ‘SalesLT’,@command = ‘GRANT’,@flag_delete = 1,@flag_execute = 1,@flag_insert = 1,@flag_select = 1,@flag_update = 1;
go
@user_db_role = ‘AW_Role’,@schema_name = NULL,@command = ‘DENY’,@flag_alter = 1;
go