Best practice for db owner.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# **Some thoughts on dbo (database owner)**\r\n",
                "\r\n",
                "Fabiano Neves Amorim - amorim@pythian.com"
            ],
            "metadata": {
                "azdata_cell_guid": "cf3c2d87-d7df-43b3-8a10-ef121bf1c50d"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Starting with a question. **Who should own a database?**\r\n",
                "\r\n",
                "By own, I mean the server principal (login) that owns the database, in other words, the login mapped to the dbo (database owner) user in a database.\r\n",
                "\r\n",
                "By default, the owner of a DB is the one that issued the create/restore database statement. Also it is important to remeber that this login will be automatically granted as db_owner database role in the DB.\r\n",
                "\r\n",
                "So, that means an old employee that created the DB 10 years ago, is probably the owner of the DB.\r\n",
                "\r\n",
                "But, the question is, is this ok have this user has a db owner?"
            ],
            "metadata": {
                "azdata_cell_guid": "e9a5987d-68c8-4f15-a1b6-f35d54470993"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Demo using a SQL login has db owner"
            ],
            "metadata": {
                "azdata_cell_guid": "8d44b1f8-a6ab-4f13-b067-e0af2ab5f12d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master\r\n",
                "GO\r\n",
                "\r\n",
                "-- Let's start by creating a dbTest1 db.\r\n",
                "DROP DATABASE IF EXISTS dbTest1\r\n",
                "CREATE DATABASE dbTest1\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "5879b3b3-7cca-4efd-9f2f-99329705ffee"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "At this time, login used to create the DB is the db owner"
            ],
            "metadata": {
                "azdata_cell_guid": "ea9fb8a4-ffd1-484b-ab3d-e28a4de04201"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT name AS dbName,\r\n",
                "     SUSER_SNAME(owner_sid) UserName,\r\n",
                "     IS_SRVROLEMEMBER('sysadmin', SUSER_SNAME(owner_sid)) is_sysadmin\r\n",
                "FROM sys.databases\r\n",
                "WHERE name = 'dbTest1';\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "2a376dd4-67f2-4cc3-b329-993b0953584c"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.090"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 2,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "dbName"
                                    },
                                    {
                                        "name": "UserName"
                                    },
                                    {
                                        "name": "is_sysadmin"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "dbTest1",
                                    "1": "RAZERFABIANO\\Fabiano",
                                    "2": "1"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>dbName</th><th>UserName</th><th>is_sysadmin</th></tr><tr><td>dbTest1</td><td>RAZERFABIANO\\Fabiano</td><td>1</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "Creating a couple of logins we'll use in the demo"
            ],
            "metadata": {
                "azdata_cell_guid": "b78b77e7-b197-4fe3-a1ed-a5f581a6f2c3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF EXISTS(SELECT * FROM sys.sql_logins WHERE name = 'TestLogin1')\r\n",
                "BEGIN\r\n",
                "  DROP LOGIN TestLogin1;\r\n",
                "  DROP LOGIN TestLogin2;\r\n",
                "END\r\n",
                "GO\r\n",
                "CREATE LOGIN TestLogin1 WITH PASSWORD = N'102030'\r\n",
                "CREATE LOGIN TestLogin2 WITH PASSWORD = N'102030'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "ebb4e6fa-28cb-481e-b527-0958fff0d49a"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Set dbo to TestLogin1"
            ],
            "metadata": {
                "azdata_cell_guid": "cae01419-39cb-4129-9ec3-feec1814776c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER AUTHORIZATION ON DATABASE::dbTest1 TO TestLogin1;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "935f9caf-df42-44f0-8610-d7e6d247a3e4",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Creating function to map Users to Role"
            ],
            "metadata": {
                "azdata_cell_guid": "b9721d63-5fb2-4059-a402-818e6c3a9cc5"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE dbTest1\r\n",
                "GO\r\n",
                "CREATE OR ALTER FUNCTION dbo.dbRolesUsersMap (@dbRole SYSNAME = '%')\r\n",
                "RETURNS TABLE\r\n",
                "AS\r\n",
                "RETURN (\r\n",
                "    SELECT \r\n",
                "      User_Type = \r\n",
                "         CASE mmbrp.[type] \r\n",
                "         WHEN 'G' THEN 'Windows Group' \r\n",
                "         WHEN 'S' THEN 'SQL User' \r\n",
                "         WHEN 'U' THEN 'Windows User' \r\n",
                "         END,\r\n",
                "       Database_User_Name = mmbrp.[name],\r\n",
                "       Login_Name = ul.[name],\r\n",
                "       DB_Role = rolp.[name]\r\n",
                "    FROM sys.database_role_members mmbr, -- The Role OR members associations table\r\n",
                "       sys.database_principals rolp,     -- The DB Roles names table\r\n",
                "       sys.database_principals mmbrp,    -- The Role members table (database users)\r\n",
                "       sys.server_principals ul          -- The Login accounts table\r\n",
                "    WHERE Upper (mmbrp.[type]) IN ( 'S', 'U', 'G' )\r\n",
                "       -- No need for these system account types\r\n",
                "       AND Upper (mmbrp.[name]) NOT IN ('SYS','INFORMATION_SCHEMA')\r\n",
                "       AND rolp.[principal_id] = mmbr.[role_principal_id]\r\n",
                "       AND mmbrp.[principal_id] = mmbr.[member_principal_id]\r\n",
                "       AND ul.[sid] = mmbrp.[sid]\r\n",
                "       AND rolp.[name] LIKE '%' + @dbRole + '%'\r\n",
                "    )\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "baa5b18f-a338-4ff6-a111-c5fbd18dcd83"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "As we can see, login TestLogin1 has db_owner on dbTest1"
            ],
            "metadata": {
                "azdata_cell_guid": "66fa528c-f162-491d-a0ae-abc0b6d637ed"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT * FROM dbTest1.dbo.dbRolesUsersMap (DEFAULT)\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "ab8a4d91-f065-48c7-8394-c07039eea6f9"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.028"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 6,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "User_Type"
                                    },
                                    {
                                        "name": "Database_User_Name"
                                    },
                                    {
                                        "name": "Login_Name"
                                    },
                                    {
                                        "name": "DB_Role"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "SQL User",
                                    "1": "dbo",
                                    "2": "TestLogin1",
                                    "3": "db_owner"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>User_Type</th><th>Database_User_Name</th><th>Login_Name</th><th>DB_Role</th></tr><tr><td>SQL User</td><td>dbo</td><td>TestLogin1</td><td>db_owner</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": [
                "At this time, we can't drop this user because it is owner of dbTest1..."
            ],
            "metadata": {
                "azdata_cell_guid": "07140a60-3b6a-4dec-b7c3-dbc7b3c7a0d0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "DROP LOGIN TestLogin1;\r\n",
                "GO\r\n",
                "--Msg 15174, Level 16, State 1, Line 75\r\n",
                "--Login 'TestLogin1' owns one or more database(s). Change the owner of the database(s) before dropping the login.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f678a712-33a3-49a5-8d27-8ed47cfeb676"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Create a test table"
            ],
            "metadata": {
                "azdata_cell_guid": "872491cd-47a2-43f9-a7fd-d4fedd4d449c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE dbTest1\r\n",
                "DROP TABLE IF EXISTS TableTest1\r\n",
                "CREATE TABLE TableTest1 (Col1 INT);\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "871dd034-f11f-4d29-8b6c-69ea50835d90"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "dbo (TestLogin1) user can do anything on DB ... "
            ],
            "metadata": {
                "azdata_cell_guid": "9dcc8354-6858-4b5c-acce-a148dc4ae7af"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master\r\n",
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "SELECT USER_NAME(), ORIGINAL_LOGIN(); -- Check user context\r\n",
                "GO\r\n",
                "SELECT Col1 FROM dbTest1.dbo.TableTest1;\r\n",
                "GO\r\n",
                "REVERT;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "e7054a11-da19-414e-b85a-1e755369b48b"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.008"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 9,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Col1"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>Col1</th></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 9,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "guest",
                                    "1": "RAZERFABIANO\\Fabiano"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>(No column name)</th></tr><tr><td>guest</td><td>RAZERFABIANO\\Fabiano</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": [
                "TestLogin2 is not allowed to access to dbTest1..."
            ],
            "metadata": {
                "azdata_cell_guid": "57e6c9e1-34c5-4c7f-a546-5b9e19472bb5"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master\r\n",
                "EXECUTE AS LOGIN = 'TestLogin2';\r\n",
                "SELECT USER_NAME(), ORIGINAL_LOGIN(); -- Check user context\r\n",
                "GO\r\n",
                "SELECT Col1 FROM dbTest1.dbo.TableTest1;\r\n",
                "--Msg 916, Level 14, State 2, Line 105\r\n",
                "--The server principal \"TestLogin2\" is not able to access the database \"dbTest1\" under the current security context.\r\n",
                "GO\r\n",
                "REVERT;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "64894e5a-c7b9-4098-83ec-eb9bdb95d31d"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "error",
                    "ename": "",
                    "evalue": "Msg 916, Level 14, State 2, Line 5\r\nThe server principal \"TestLogin2\" is not able to access the database \"dbTest1\" under the current security context.",
                    "traceback": []
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 10,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "guest",
                                    "1": "RAZERFABIANO\\Fabiano"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>(No column name)</th></tr><tr><td>guest</td><td>RAZERFABIANO\\Fabiano</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 10
        },
        {
            "cell_type": "markdown",
            "source": [
                "First lesson, application shouldn't connect using db owner login because this has a lot more privileges that are necessary.\r\n",
                "\r\n",
                "A good practice would be disable this user to guarantee no one is logging with it.\r\n",
                "\r\n",
                "**Note:** Users can still use a disabled user with \"EXECUTE AS\"..."
            ],
            "metadata": {
                "azdata_cell_guid": "e06df050-f4bb-4ef9-a647-9c1ce004dc42"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Demo using a Windows Account has db owner"
            ],
            "metadata": {
                "azdata_cell_guid": "b1f8d9eb-2048-4cf2-836e-2dd67dba0f9c"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "An windows account looks fine, but what happens when this account is removed? \r\n",
                "\r\n",
                "Would this be a problem?"
            ],
            "metadata": {
                "azdata_cell_guid": "e06a6709-3873-4751-9bc9-6cc10715c933"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master\r\n",
                "GO\r\n",
                "-- Creating a Win account we'll use in this test...\r\n",
                "EXEC xp_cmdshell 'NET USER WinAccountLogin1 /DELETE'\r\n",
                "EXEC xp_cmdshell 'NET USER WinAccountLogin1 102030 /ADD'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "7f64ef4e-3b37-448a-a9f1-9218be9ecb4b"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.893"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 11,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "output"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "The command completed successfully."
                                },
                                {
                                    "0": "NULL"
                                },
                                {
                                    "0": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>output</th></tr><tr><td>The command completed successfully.</td></tr><tr><td>NULL</td></tr><tr><td>NULL</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 11,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "output"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "The command completed successfully."
                                },
                                {
                                    "0": "NULL"
                                },
                                {
                                    "0": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>output</th></tr><tr><td>The command completed successfully.</td></tr><tr><td>NULL</td></tr><tr><td>NULL</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's start by creating a dbTest1 db."
            ],
            "metadata": {
                "azdata_cell_guid": "e790bf23-8abc-4dae-854e-1230ffcb382d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "DROP DATABASE IF EXISTS dbTest1\r\n",
                "CREATE DATABASE dbTest1\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "a850cddf-d549-440b-94c4-9882852e29c9"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Create a SQL Login for this account"
            ],
            "metadata": {
                "azdata_cell_guid": "4174effb-762d-48f8-bf22-e5603eedc5a8"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF EXISTS(SELECT * FROM sys.server_principals WHERE name = 'RAZERFABIANO\\WinAccountLogin1')\r\n",
                "  DROP LOGIN [RAZERFABIANO\\WinAccountLogin1]\r\n",
                "GO\r\n",
                "CREATE LOGIN [RAZERFABIANO\\WinAccountLogin1] FROM WINDOWS\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "86bc7edb-88ab-49e0-8b10-0d7126d8445a"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Set dbo to [RAZERFABIANO\\WinAccountLogin1]"
            ],
            "metadata": {
                "azdata_cell_guid": "1f13cf3f-a4b1-4a92-9c4f-69b8aa023d83"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER AUTHORIZATION ON DATABASE::dbTest1 TO [RAZERFABIANO\\WinAccountLogin1];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "958f3c25-df5f-44ce-adda-53720cf3db97"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Creating a table to do some tests"
            ],
            "metadata": {
                "azdata_cell_guid": "12eaf339-2e84-48b6-b89d-3d3d898063c8"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE dbTest1\r\n",
                "DROP TABLE IF EXISTS TableTest1\r\n",
                "CREATE TABLE TableTest1 (Col1 INT);\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "06cd5569-ce51-4f9e-b51c-343d2ace34b1"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Creating a proc to run as owner"
            ],
            "metadata": {
                "azdata_cell_guid": "5fe2911c-123f-4979-843f-e374b250c5a3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "DROP PROC IF EXISTS Proc1\r\n",
                "GO\r\n",
                "CREATE PROC Proc1\r\n",
                "WITH EXECUTE AS OWNER\r\n",
                "AS\r\n",
                "SELECT USER_NAME(), ORIGINAL_LOGIN(); -- Check user context\r\n",
                "SELECT * FROM TableTest1\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "6580a072-8a2a-42b5-8a71-a423372ff967"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Creating a login with access only to connect into the DB and run the SP..."
            ],
            "metadata": {
                "azdata_cell_guid": "127b0520-b96a-4bcd-bed0-f8f5065b2dc5"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF EXISTS(SELECT * FROM sys.sql_logins WHERE name = 'TestLogin1')\r\n",
                "  DROP LOGIN TestLogin1;\r\n",
                "GO\r\n",
                "CREATE LOGIN TestLogin1 WITH PASSWORD = N'102030'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "c91fa02e-7499-4b26-8605-643e39ebccd8"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Grant DB access and EXEC on Proc1"
            ],
            "metadata": {
                "azdata_cell_guid": "efd5b76e-8cff-41c4-976b-fd9aee5e9a51"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE dbTest1\r\n",
                "CREATE USER TestLogin1 FOR LOGIN TestLogin1\r\n",
                "GRANT EXECUTE ON OBJECT::dbo.Proc1 TO TestLogin1;  \r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "093ba670-f2fe-4c6f-9397-d84bf78e2e53"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "If I try to run the SP using TestLogin1 it should work"
            ],
            "metadata": {
                "azdata_cell_guid": "cc400bc6-f448-4160-956d-d84c55fe4f5d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "GO\r\n",
                "EXEC dbTest1.dbo.Proc1\r\n",
                "GO\r\n",
                "REVERT\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "6aaf2bc8-07ed-458b-86db-a034e41a8a9c"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.010"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 19,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "dbo",
                                    "1": "RAZERFABIANO\\Fabiano"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>(No column name)</th></tr><tr><td>dbo</td><td>RAZERFABIANO\\Fabiano</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 19,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Col1"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>Col1</th></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 19
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's remove Windows user"
            ],
            "metadata": {
                "azdata_cell_guid": "b7f0f446-5a8d-4ee2-8e3e-7cce36f4b7fd"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXEC xp_cmdshell 'NET USER WinAccountLogin1 /DELETE'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "087b5f3f-e890-4d86-b9d2-8d0f20a691bf"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.395"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 20,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "output"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "The command completed successfully."
                                },
                                {
                                    "0": "NULL"
                                },
                                {
                                    "0": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>output</th></tr><tr><td>The command completed successfully.</td></tr><tr><td>NULL</td></tr><tr><td>NULL</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 20
        },
        {
            "cell_type": "markdown",
            "source": [
                "Now EXECUTE AS proc will fail..."
            ],
            "metadata": {
                "azdata_cell_guid": "ce0d2443-881c-4086-9fae-1c847c66578c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "GO\r\n",
                "EXEC dbTest1.dbo.Proc1\r\n",
                "GO\r\n",
                "REVERT\r\n",
                "GO\r\n",
                "--Msg 15404, Level 16, State 11, Procedure dbTest1.dbo.Proc1, Line 0 [Batch Start Line 216]\r\n",
                "--Could not obtain information about Windows NT group/user 'RAZERFABIANO\\WinAccountLogin1', error code 0x534."
            ],
            "metadata": {
                "azdata_cell_guid": "0b5435b1-a127-4527-9bc4-e749870897b7"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "error",
                    "ename": "",
                    "evalue": "Msg 15404, Level 16, State 11, Procedure dbTest1.dbo.Proc1, Line 0\r\nCould not obtain information about Windows NT group/user 'RAZERFABIANO\\WinAccountLogin1', error code 0x534.",
                    "traceback": []
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.001"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 21
        },
        {
            "cell_type": "markdown",
            "source": [
                "Add user again will \"fix\" it..."
            ],
            "metadata": {
                "azdata_cell_guid": "6597e707-c329-4b9a-8dfc-89685f43cab3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXEC xp_cmdshell 'NET USER WinAccountLogin1 102030 /ADD'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "0000586a-63a8-4871-82c6-ed063819183b"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.370"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 22,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "output"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "The command completed successfully."
                                },
                                {
                                    "0": "NULL"
                                },
                                {
                                    "0": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>output</th></tr><tr><td>The command completed successfully.</td></tr><tr><td>NULL</td></tr><tr><td>NULL</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 22
        },
        {
            "cell_type": "markdown",
            "source": [
                " Now it works..."
            ],
            "metadata": {
                "azdata_cell_guid": "6827d290-f1aa-407f-82ed-e3f64fac4f12"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "GO\r\n",
                "EXEC dbTest1.dbo.Proc1\r\n",
                "GO\r\n",
                "REVERT\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "6a68a679-3d7c-4ae1-9675-4cbb31c5fb94"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.009"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 23,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "RAZERFABIANO\\WinAccountLogin1",
                                    "1": "RAZERFABIANO\\Fabiano"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>(No column name)</th></tr><tr><td>RAZERFABIANO\\WinAccountLogin1</td><td>RAZERFABIANO\\Fabiano</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 23,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Col1"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>Col1</th></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 23
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's create a DB backup to restore after we drop the Login..."
            ],
            "metadata": {
                "azdata_cell_guid": "b3a63db5-7a60-452f-9766-4c342b34653f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master\r\n",
                "GO\r\n",
                "EXEC xp_cmdshell 'del C:\\temp\\dbTest1.bak'\r\n",
                "BACKUP DATABASE [dbTest1] TO  DISK = N'C:\\temp\\dbTest1.bak' \r\n",
                "WITH NOFORMAT, NOINIT,  NAME = N'dbTest1-Full Database Backup', \r\n",
                "SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10\r\n",
                "GO\r\n",
                "DROP DATABASE IF EXISTS dbTest1\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "4a3306f4-8337-4dc0-bbea-9b7962090616"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Removing Login RAZERFABIANO\\WinAccountLogin1\r\n",
                "To simulate a scenario where DB will be restored in a new Instance \r\n",
                "where user was not created"
            ],
            "metadata": {
                "azdata_cell_guid": "62a5f97e-8827-4f19-baff-b341e03c5ecd"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "DROP LOGIN [RAZERFABIANO\\WinAccountLogin1];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "ec39bacd-e0e8-4653-95ed-9841b082c69e"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "What will happen if we restore the DB? "
            ],
            "metadata": {
                "azdata_cell_guid": "ed934977-96ca-467d-891b-1f38b1e53dda"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "RESTORE DATABASE [dbTest1] FROM  DISK = N'c:\\temp\\dbTest1.bak' \r\n",
                "WITH  FILE = 1,  NOUNLOAD,  STATS = 5\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "673396cb-b9b0-458e-90e1-951015afff42"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Now it fails because logging specified as db owner doesn't exist"
            ],
            "metadata": {
                "azdata_cell_guid": "25ba1d84-c210-495c-9e27-944e6be58cd2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "GO\r\n",
                "EXEC dbTest1.dbo.Proc1\r\n",
                "GO\r\n",
                "REVERT\r\n",
                "GO\r\n",
                "-- Msg 15517, Level 16, State 1, Procedure dbTest1.dbo.Proc1, Line 0 [Batch Start Line 264]\r\n",
                "-- Cannot execute as the database principal because the principal \"dbo\" does not exist, \r\n",
                "-- this type of principal cannot be impersonated, or you do not have permission."
            ],
            "metadata": {
                "azdata_cell_guid": "8b3b4521-d098-4bd9-a3d3-35f19977872a"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "error",
                    "ename": "",
                    "evalue": "Msg 15517, Level 16, State 1, Procedure dbTest1.dbo.Proc1, Line 0\r\nCannot execute as the database principal because the principal \"dbo\" does not exist, this type of principal cannot be impersonated, or you do not have permission.",
                    "traceback": []
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.004"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 27
        },
        {
            "cell_type": "markdown",
            "source": [
                "But, wait, when I restored the DB shouldn't it change the owner to the user running the \r\n",
                "RESTORE DATABASE command? \r\n",
                "\r\n",
                "Let's check who is the DB owner"
            ],
            "metadata": {
                "azdata_cell_guid": "a8fca4ab-8ead-4744-b1ad-daf5fe4bf640"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "-- Owner according to master is \"RAZERFABIANO\\Fabiano\", that is the \r\n",
                "-- user that ran RESTORE DATABASE command\r\n",
                "SELECT SUSER_SNAME(owner_sid), owner_sid FROM sys.databases WHERE name = 'dbTest1'\r\n",
                "\r\n",
                "-- Owner according to database is unknown as sid doesn't map to any existing login\r\n",
                "-- Classic orphaned user case, right?...\r\n",
                "SELECT SUSER_SNAME(sid), sid FROM dbTest1.sys.database_principals WHERE name = 'dbo'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "2cb1cfef-5116-42d1-941c-cf9f4948751b"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.041"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 28,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "owner_sid"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "RAZERFABIANO\\Fabiano",
                                    "1": "0x01050000000000051500000067690A979A5095932F9AF370E9030000"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>owner_sid</th></tr><tr><td>RAZERFABIANO\\Fabiano</td><td>0x01050000000000051500000067690A979A5095932F9AF370E9030000</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 28,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "sid"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "NULL",
                                    "1": "0x01050000000000051500000067690A979A5095932F9AF37016040000"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>sid</th></tr><tr><td>NULL</td><td>0x01050000000000051500000067690A979A5095932F9AF37016040000</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 28
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's check the orphaned users report"
            ],
            "metadata": {
                "azdata_cell_guid": "1e6efa1e-51ac-4976-92ac-fc7e0cc6e4b3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE dbTest1\r\n",
                "EXEC sp_change_users_login 'Report'\r\n",
                "GO\r\n",
                "-- Ops no results, yep, that's right, there is no RAZERFABIANO\\WinAccountLogin1 user"
            ],
            "metadata": {
                "azdata_cell_guid": "9ed84d37-b954-4eb1-90ec-a531ca017746"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 29,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "UserName"
                                    },
                                    {
                                        "name": "UserSID"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>UserName</th><th>UserSID</th></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.053"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 29
        },
        {
            "cell_type": "markdown",
            "source": [
                "Ok, what if we recreate the Login?"
            ],
            "metadata": {
                "azdata_cell_guid": "6fe75bc2-6e81-4c99-bb0e-d25d037fbc79"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE LOGIN [RAZERFABIANO\\WinAccountLogin1] FROM WINDOWS\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "027d3372-aebb-4ca4-a38d-5c2dfbe1bd62"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Still no luck"
            ],
            "metadata": {
                "azdata_cell_guid": "5a833b26-bfc2-4379-8ad9-da0ee472abfc"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "GO\r\n",
                "EXEC dbTest1.dbo.Proc1\r\n",
                "GO\r\n",
                "REVERT\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "ccb30e14-b604-4bb9-88ff-ad27e6ead665"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "error",
                    "ename": "",
                    "evalue": "Msg 15517, Level 16, State 1, Procedure dbTest1.dbo.Proc1, Line 0\r\nCannot execute as the database principal because the principal \"dbo\" does not exist, this type of principal cannot be impersonated, or you do not have permission.",
                    "traceback": []
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.002"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 31
        },
        {
            "cell_type": "markdown",
            "source": [
                "That's because SID of new login is different that SID on database"
            ],
            "metadata": {
                "azdata_cell_guid": "5cb00ed1-4236-4343-8772-0184049639ba"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT name, sid FROM sys.server_principals WHERE name = 'RAZERFABIANO\\WinAccountLogin1'\r\n",
                "-- 0x01050000000000051500000067690A979A5095932F9AF37015040000\r\n",
                "GO\r\n",
                "SELECT SUSER_SNAME(sid), sid FROM dbTest1.sys.database_principals WHERE name = 'dbo'\r\n",
                "-- 0x01050000000000051500000067690A979A5095932F9AF37014040000\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "b3b02621-f435-4e3c-8b39-6012ae0c6f66"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.020"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 32,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "name"
                                    },
                                    {
                                        "name": "sid"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "RAZERFABIANO\\WinAccountLogin1",
                                    "1": "0x01050000000000051500000067690A979A5095932F9AF37017040000"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>name</th><th>sid</th></tr><tr><td>RAZERFABIANO\\WinAccountLogin1</td><td>0x01050000000000051500000067690A979A5095932F9AF37017040000</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 32,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "sid"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "NULL",
                                    "1": "0x01050000000000051500000067690A979A5095932F9AF37016040000"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>sid</th></tr><tr><td>NULL</td><td>0x01050000000000051500000067690A979A5095932F9AF37016040000</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 32
        },
        {
            "cell_type": "markdown",
            "source": [
                "If we to set owner back to [RAZERFABIANO\\WinAccountLogin1] it will update the SID and fix the issue..."
            ],
            "metadata": {
                "azdata_cell_guid": "64b35713-4afd-4f74-a700-98e4b309ada6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER AUTHORIZATION ON DATABASE::dbTest1 TO [RAZERFABIANO\\WinAccountLogin1];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "0368b6f4-9be7-42e5-978b-24f604921f3f"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Now it works..."
            ],
            "metadata": {
                "azdata_cell_guid": "bc829df6-c52e-4e47-bbae-51fdac345a0a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "GO\r\n",
                "EXEC dbTest1.dbo.Proc1\r\n",
                "GO\r\n",
                "REVERT\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "02860fbf-5322-48b3-bb96-f4a3f9155a86"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.010"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 34,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "dbo",
                                    "1": "RAZERFABIANO\\Fabiano"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>(No column name)</th></tr><tr><td>dbo</td><td>RAZERFABIANO\\Fabiano</td></tr></table>"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 34,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Col1"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>Col1</th></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 34
        },
        {
            "cell_type": "markdown",
            "source": [
                "So, what people do to avoid those problems? \r\n",
                "Easy, set db owner to sa.\r\n",
                "  \r\n",
                "But, is this really a good option?\r\n",
                "I understand it is \"easier\" from a maintenance perspective to use sa has the owner, but there is a risk of elevation of privilege and it could be worse in case the DB is set to trustworthy.\r\n",
                "\r\n",
                "From that perspective, **a risk is a risk** and we need to honor the basic **PoLP**(**P**rinciple of **L**east **P**rivilege).\r\n",
                "Means, an user must be able to access and should only have privileges to information/resources that are necessary for its legitimate purpose."
            ],
            "metadata": {
                "azdata_cell_guid": "81d3a9e2-7633-4e70-9b4a-a1dd3b808d56"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Demo, using SA has db owner"
            ],
            "metadata": {
                "azdata_cell_guid": "1d094b35-d1af-4dda-9c05-c49e8f6e5ad6"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Remember, usually an attack starts from inside, an employee not happy with latest bonus or with bad intention may decide to do things he shouldn't be able to do unless sysadmin privileged is available... Well, it really doesn’t matter whether user has sysadmin role or even if the sa account is disabled.\r\n",
                "If sa owns a DB, an attacker could escalate privilege to sysadmin...\r\n",
                "\r\n",
                "Let's see a sample...\r\n",
                "\r\n",
                "Let's consider a scenario where you've a Junior DBA or an application login with db_owner role in a DB.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "e52fe10d-7632-44bb-8efa-9b5339d628b3"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's start by creating a dbTest1 db.\r\n",
                "\r\n",
                "Notice that I'm setting DB to trustworthy, this is required to escalate the privilege.  "
            ],
            "metadata": {
                "azdata_cell_guid": "1210d5ee-0062-4040-b5fc-2d1083934291"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master\r\n",
                "GO\r\n",
                "DROP DATABASE IF EXISTS dbTest1\r\n",
                "CREATE DATABASE dbTest1\r\n",
                "ALTER DATABASE dbTest1 SET TRUSTWORTHY ON\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "2d9403d1-54bf-4559-8dd4-d78bd2242c5d"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Creating a login we'll use in the demo"
            ],
            "metadata": {
                "azdata_cell_guid": "fe9dbd4e-dd9e-4c32-8ef2-b1d4d4ed5225"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF EXISTS(SELECT * FROM sys.sql_logins WHERE name = 'TestLogin1')\r\n",
                "DROP LOGIN TestLogin1;\r\n",
                "GO\r\n",
                "CREATE LOGIN TestLogin1 WITH PASSWORD = N'102030'\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "9138a766-bd1f-4890-8c7e-4c88004e21c7"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Set dbo to sa"
            ],
            "metadata": {
                "azdata_cell_guid": "20b1c5de-3b29-4d92-9d28-198ea64eb353"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER AUTHORIZATION ON DATABASE::dbTest1 TO sa;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "72e535c9-6db9-4480-a93c-4472b769ab28"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Grant TestLogin1 login DB access and db_ddladmin role"
            ],
            "metadata": {
                "azdata_cell_guid": "c6b0a8d4-9b81-456a-8cce-b66ae4d257b7"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE dbTest1\r\n",
                "DROP USER IF EXISTS TestLogin1\r\n",
                "CREATE USER TestLogin1 FOR LOGIN TestLogin1;\r\n",
                "GO\r\n",
                "EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'TestLogin1';\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "38b134f0-cdf7-460c-bcd0-62c36eb59310"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "What if I try to run xp_cmdshell to create a local admin user?\r\n",
                "User should not have access to xp_cmdshell and will receive permission denied message..."
            ],
            "metadata": {
                "azdata_cell_guid": "61710b54-b4d2-4065-8c57-c47df33a764b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "SELECT USER_NAME(), ORIGINAL_LOGIN(); -- Check user context\r\n",
                "EXEC xp_cmdshell 'echo \"Hello world\"'\r\n",
                "GO\r\n",
                "REVERT;\r\n",
                "GO\r\n",
                "--Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 [Batch Start Line 382]\r\n",
                "--The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'."
            ],
            "metadata": {
                "azdata_cell_guid": "f2bce3ee-7f76-47e2-b0b6-2bf457b777ff"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "error",
                    "ename": "",
                    "evalue": "Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1\r\nThe EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.",
                    "traceback": []
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 40,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "TestLogin1",
                                    "1": "RAZERFABIANO\\Fabiano"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>(No column name)</th></tr><tr><td>TestLogin1</td><td>RAZERFABIANO\\Fabiano</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 40
        },
        {
            "cell_type": "markdown",
            "source": [
                "But I can create a procedure... right? I'm a db_owner...\r\n",
                "So, let's create a special proc"
            ],
            "metadata": {
                "azdata_cell_guid": "28aac9ae-090b-4f52-850e-a464866a81ec"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "DROP PROC IF EXISTS my_xp_cmdshell\r\n",
                "GO\r\n",
                "CREATE PROC my_xp_cmdshell @Str VARCHAR(8000)\r\n",
                "WITH EXECUTE AS OWNER\r\n",
                "AS\r\n",
                "EXEC xp_cmdshell @Str\r\n",
                "GO\r\n",
                "REVERT;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "3d3d4f09-9913-4a6a-81a5-faf063ee588b"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Now I've my own xp_cmdshell version..."
            ],
            "metadata": {
                "azdata_cell_guid": "853af6e0-e0ce-42fe-b08b-dce58153617d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "EXECUTE AS LOGIN = 'TestLogin1';\r\n",
                "EXEC my_xp_cmdshell @Str = 'echo \"Hello world\"'\r\n",
                "GO\r\n",
                "REVERT;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "8ff67d0d-be70-4398-bf88-44a57ae809fc"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.259"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 42,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "output"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "\"Hello world\""
                                },
                                {
                                    "0": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>output</th></tr><tr><td>&quot;Hello world&quot;</td></tr><tr><td>NULL</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 42
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Final recommendations:\r\n",
                "\r\n",
                "- As a best practice, we should use either a Windows Account or a disabled \r\n",
                "SQL Login with very minimum permission to minimize risks of priv. escalation, \r\n",
                "even in scenarios where a DB is set to trustworthy. An instance may not be vulnerably at the time we check, but as soon as someone (a Dev trying to create an unsafe CLR?...) set a DB to trustworthy...\r\n",
                "\r\n",
                "- Disable the trustworthy from all (except MSDB) databases. If you need to use functionality that is controlled by the trustworthy option, use digital signatures to enable the functionality.\r\n",
                "\r\n",
                "Links:\r\n",
                "\r\n",
                "https://support.microsoft.com/en-us/help/2183687/guidelines-for-using-the-trustworthy-database-setting-in-sql-server\r\n",
                "\r\n",
                "https://docs.microsoft.com/en-us/archive/blogs/sqlsecurity/the-trustworhy-bit-database-property-in-sql-server-2005\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "cddce6c0-c2f9-4bff-a09f-2b7dcd656b69"
            }
        }
    ]
}