{"id":1669,"date":"2023-04-22T23:43:33","date_gmt":"2023-04-22T21:43:33","guid":{"rendered":"https:\/\/alessandromasciadri.com\/?p=1669"},"modified":"2023-04-22T23:53:24","modified_gmt":"2023-04-22T21:53:24","slug":"come-aggiungere-utente-azure-active-directory-ad-un-azure-sql-database","status":"publish","type":"post","link":"https:\/\/alessandromasciadri.com\/come-aggiungere-utente-azure-active-directory-ad-un-azure-sql-database\/","title":{"rendered":"Come aggiungere utente Azure Active Directory ad un Azure SQL Database"},"content":{"rendered":"\t\t<div data-akihiro-type=\"ama-post\" data-akihiro-id=\"1669\" class=\"akihiro akihiro-1669\" data-akihiro-post-type=\"post\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-46b6805 e-flex e-con-boxed e-con e-parent\" data-id=\"46b6805\" data-element_type=\"container\" data-e-type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5d4bbd6 akihiro-widget akihiro-widget-text-editor\" data-id=\"5d4bbd6\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Abbiamo un utente Azure Active Directory a cui vogliamo assegnare determinati permessi ad un database SQL creato su Azure. Seguiamo questa guida passo a passo per concedere all&#8217;utente i permessi desiderati.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-6fe6cf9 akihiro-widget akihiro-widget-heading\" data-id=\"6fe6cf9\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Azure Active Directory Authentication<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-34eeb95 akihiro-widget akihiro-widget-text-editor\" data-id=\"34eeb95\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Nel nostro scenario la connessione al Database Server \u00e8 concessa solo agli utenti di Azure Active Directory.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-378f63e akihiro-widget akihiro-widget-image\" data-id=\"378f63e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img fetchpriority=\"high\" decoding=\"async\" width=\"800\" height=\"144\" src=\"https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-01-1024x184.png\" class=\"attachment-large size-large ama-image-1671\" alt=\"\" srcset=\"https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-01-1024x184.png 1024w, https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-01-300x54.png 300w, https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-01-768x138.png 768w, https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-01-1536x277.png 1536w, https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-01.png 1582w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-d81163a akihiro-widget akihiro-widget-heading\" data-id=\"d81163a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Connessione al database<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-24958b2 akihiro-widget akihiro-widget-text-editor\" data-id=\"24958b2\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Come prima cosa, utilizzando Azure Data Studio o SSMS, colleghiamoci con il nostro utente amministratore al database al quale vogliamo aggiungere l&#8217;utente e concedergli i privilegi.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-8147026 akihiro-widget akihiro-widget-image\" data-id=\"8147026\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"306\" height=\"367\" src=\"https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-02.png\" class=\"attachment-large size-large ama-image-1672\" alt=\"\" srcset=\"https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-02.png 306w, https:\/\/alessandromasciadri.com\/ama-uploads\/2023\/04\/azure-sql-permissions-02-250x300.png 250w\" sizes=\"(max-width: 306px) 100vw, 306px\" \/>\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-b9526a3 akihiro-widget akihiro-widget-heading\" data-id=\"b9526a3\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Creazione dell'utente<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-fa397bf akihiro-widget akihiro-widget-text-editor\" data-id=\"fa397bf\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Dobbiamo a ora creare l&#8217;utente nel nostro database. Apriamo una finestra di Query e lanciamo questo comando:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-6e087be akihiro-widget akihiro-widget-code-highlight\" data-id=\"6e087be\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>CREATE USER [username@your-domain.com] FROM EXTERNAL PROVIDER;\nGO<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-f8ef695 akihiro-widget akihiro-widget-text-editor\" data-id=\"f8ef695\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Ottenendo il messaggio seguente:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-8e07eaf akihiro-widget akihiro-widget-code-highlight\" data-id=\"8e07eaf\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language- \">\n\t\t\t\t<code readonly=\"true\" class=\"language-\">\n\t\t\t\t\t<xmp>8:19:10 PMStarted executing query at Line 1\n        Commands completed successfully.\n        Total execution time: 00:00:00.472<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e4caccf akihiro-widget akihiro-widget-heading\" data-id=\"e4caccf\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Attribuzione dei privilegi<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-daa7321 akihiro-widget akihiro-widget-text-editor\" data-id=\"daa7321\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>A questo punto non ci resta che attribuire all&#8217;utente appena creato i permessi desiderati lanciando questo comando:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-ccc427b akihiro-widget akihiro-widget-code-highlight\" data-id=\"ccc427b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>ALTER ROLE db_datawriter ADD MEMBER [username@your-domain.com];\nGO\n<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-7e42be9 akihiro-widget akihiro-widget-heading\" data-id=\"7e42be9\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Elenco dei ruoli disponibili<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-881e35f akihiro-widget akihiro-widget-text-editor\" data-id=\"881e35f\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>La seguente tabella dettaglia i ruoli disponibili in MSSQL. Per maggiori dettagli si rinvia alla <a href=\"https:\/\/learn.microsoft.com\/it-it\/sql\/relational-databases\/security\/authentication-access\/database-level-roles?view=sql-server-ver16\">documentazione ufficiale Microsoft<\/a>.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-2e16f7b akihiro-widget akihiro-widget-text-editor\" data-id=\"2e16f7b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<table><thead><tr><th>Nome del ruolo predefinito del database<\/th><th>Descrizione<\/th><\/tr><\/thead><tbody><tr><td><strong>db_owner<\/strong><\/td><td>I membri del <strong>db_owner<\/strong> ruolo predefinito del database possono<br \/>eseguire tutte le attivit\u00e0 di configurazione e manutenzione nel database <code>drop<\/code><br \/>e anche il database in SQL Server. In Database SQL e Azure Synapse alcune attivit\u00e0<br \/>di manutenzione richiedono autorizzazioni a livello di server e non possono essere<br \/>eseguite da ruoli <strong>db_owners<\/strong>.<\/td><\/tr><tr><td><strong>db_securityadmin<\/strong><\/td><td>I membri del ruolo predefinito del database <strong>db_securityadmin<\/strong><br \/>possono modificare le appartenenze al ruolo solo per i ruoli personalizzati e<br \/>gestire le autorizzazioni. I membri di questo ruolo possono potenzialmente elevare i<br \/>propri privilegi ed \u00e8 consigliabile monitorarne le azioni.<\/td><\/tr><tr><td><strong>db_accessadmin<\/strong><\/td><td>I membri del ruolo predefinito del database <strong>db_accessadmin<\/strong> possono<br \/>aggiungere o rimuovere le autorizzazioni di accesso al database per gli account di<br \/>accesso di Windows, i gruppi di Windows e gli account di accesso di SQL Server .<\/td><\/tr><tr><td><strong>db_backupoperator<\/strong><\/td><td>I membri del ruolo predefinito del database <strong>db_backupoperator<\/strong><br \/>possono eseguire il backup del database.<\/td><\/tr><tr><td><strong>db_ddladmin<\/strong><\/td><td>I membri del ruolo predefinito del database <strong>db_ddladmin<\/strong> possono<br \/>eseguire qualsiasi comando DDL (Data Definition Language) in un database.<\/td><\/tr><tr><td><strong>db_datawriter<\/strong><\/td><td>I membri del ruolo predefinito del database <strong>db_datawriter<\/strong> possono<br \/>aggiungere, eliminare o modificare i dati di tutte le tabelle utente.<\/td><\/tr><tr><td><strong>db_datareader<\/strong><\/td><td>I membri del <strong>db_datareader<\/strong> ruolo predefinito del database possono<br \/>leggere tutti i dati da tutte le tabelle e viste utente. Gli oggetti utente possono<br \/>esistere in qualsiasi schema, ad eccezione <em>di sys<\/em> <em>e INFORMATION_SCHEMA<\/em><br \/>.<\/td><\/tr><tr><td><strong>db_denydatawriter<\/strong><\/td><td>I membri del ruolo predefinito del database <strong>db_denydatawriter<\/strong> non<br \/>possono aggiungere, modificare o eliminare dati delle tabelle utente contenute in un<br \/>database.<\/td><\/tr><tr><td><strong>db_denydatareader<\/strong><\/td><td>I <strong>membri del db_denydatareader<\/strong> ruolo predefinito del database non<br \/>possono leggere dati dalle tabelle utente e dalle viste all&#8217;interno di un database.<\/td><\/tr><\/tbody><\/table>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-f50b320 akihiro-widget akihiro-widget-heading\" data-id=\"f50b320\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Revoca dei privilegi<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-2d7e1ff akihiro-widget akihiro-widget-text-editor\" data-id=\"2d7e1ff\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Nel caso in cui vogliamo revocare i privilegi attribuiti all&#8217;utente, possiamo utilizzare il seguente comando:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-3f423e9 akihiro-widget akihiro-widget-code-highlight\" data-id=\"3f423e9\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>ALTER ROLE db_datawriter DROP MEMBER [username@your-domain.com];\nGO<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-d682a12 akihiro-widget akihiro-widget-heading\" data-id=\"d682a12\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Eliminazione dell'utente<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-648b0f9 akihiro-widget akihiro-widget-text-editor\" data-id=\"648b0f9\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Per eliminare definitivamente l&#8217;utente dal nostro database, possiamo utilizzare questo comando:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-153d1e5 akihiro-widget akihiro-widget-code-highlight\" data-id=\"153d1e5\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>DROP USER [username@your-domain.com]\nGO<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Abbiamo un utente Azure Active Directory a cui vogliamo assegnare determinati permessi ad un database SQL creato su Azure. Seguiamo questa guida passo a passo per concedere all&#8217;utente i permessi desiderati. Azure Active Directory Authentication Nel nostro scenario la connessione al Database Server \u00e8 concessa solo agli utenti di Azure Active Directory. Connessione al database [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[49,48],"class_list":["post-1669","post","type-post","status-publish","format-standard","hentry","category-sistemistica","tag-azure","tag-microsoft365"],"_links":{"self":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1669","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/comments?post=1669"}],"version-history":[{"count":7,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1669\/revisions"}],"predecessor-version":[{"id":1679,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1669\/revisions\/1679"}],"wp:attachment":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/media?parent=1669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/categories?post=1669"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/tags?post=1669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}