Scripting object permissions independently

Es ist keine Repository-Datei, kein Test und kein Einstiegspunkt angegeben; beginne damit, ObjectPermissionInfo und die vorhandene Scripting- oder Berechtigungs-API im Repository zu finden. Vergleiche die vorgeschlagene Behandlung von Objektklassen in SQL Server mit dem aktuellen Verhalten und der Dokumentation und lege anschließend unterstützte Fälle und Tests fest, bevor du entscheidest, ob die Konvertierung vollständig ist.

@shueybubbles I wrote this code awhile ago to integrate with SMO and overcome limitations in the built-in scripting engine (note the LOL). Can I get some initial feedback on how I can convert this into a PR? I am sure it's missing a bunch of edge cases.

public static class ObjectPermissionInfoExtensions
    {
        public static string ToSqlString(this ObjectPermissionInfo opi)
        {
            if (opi is null) throw new ArgumentNullException(nameof(opi));
            string toTarget;
            switch (opi.GranteeType)
            {
                case PrincipalType.None:
                    throw new Exception("What the heck happened?");
                case PrincipalType.Login:
                case PrincipalType.ServerRole:
                case PrincipalType.User:
                case PrincipalType.DatabaseRole:
                case PrincipalType.ApplicationRole:
                default:
                    toTarget = $"/*{opi.GranteeType}::*/[{opi.Grantee}]";
                    break;
            };

            string permissionedObjectExpression;
            switch (opi.ObjectClass)
            {
                case ObjectClass.Database:
                    permissionedObjectExpression = $"ON {opi.ObjectClass}::[{opi.ObjectName}]";
                    break;
                case ObjectClass.ObjectOrColumn:
                    // ex A: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver15#a-granting-select-permission-on-a-table
                    // ex B: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver15#b-granting-execute-permission-on-a-stored-procedure
                    permissionedObjectExpression = string.IsNullOrWhiteSpace(opi.ColumnName)
                        ? $"ON OBJECT::[{opi.ObjectSchema}].[{opi.ObjectName}]"
                        : $"ON OBJECT::[{opi.ObjectSchema}].[{opi.ObjectName}]([{opi.ColumnName}])";
                    break;
                case ObjectClass.Schema:
                    permissionedObjectExpression = $"ON {opi.ObjectClass}::[{opi.ObjectName}]";
                    break;
                case ObjectClass.User:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON USER::[{opi.ObjectName}]";
                    break;
                case ObjectClass.DatabaseRole:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON ROLE::[{opi.ObjectName}]";
                    break;
                case ObjectClass.ApplicationRole:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON APPLICATION ROLE::[{opi.ObjectName}]";
                    break;
                case ObjectClass.SqlAssembly:
                    permissionedObjectExpression = $"ASSEMBLY [{opi.ObjectName}]";
                    break;
                case ObjectClass.UserDefinedType:
                    permissionedObjectExpression = $"ON TYPE::[{opi.ObjectSchema}].[{opi.ObjectName}]";
                    break;
                case ObjectClass.SecurityExpression:
                case ObjectClass.XmlNamespace:
                case ObjectClass.MessageType:
                case ObjectClass.ServiceContract:
                case ObjectClass.Service:
                case ObjectClass.RemoteServiceBinding:
                case ObjectClass.ServiceRoute:
                    throw new Exception($"{opi.ObjectClass} is in set of object classes not supported.");
                case ObjectClass.FullTextCatalog:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-full-text-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON FULLTEXT CATALOG :: {opi.ObjectName}";
                    break;
                case ObjectClass.SearchPropertyList:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-search-property-list-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON SEARCH PROPERTY LIST :: {opi.ObjectName}";
                    break;
                case ObjectClass.SymmetricKey:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-symmetric-key-permissions-transact-sql?view=sql-server-ver15#examples
                    permissionedObjectExpression = $"ON SYMMETRIC KEY {opi.ObjectName}";
                    break;
                case ObjectClass.Server:
                    // This won't work for every SERVER permission, but it at least covers one scenario:
                    // ex A: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql-server-ver15#a-granting-a-permission-to-a-login
                    permissionedObjectExpression = "SERVER";
                    break;
                case ObjectClass.Login:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-principal-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON LOGIN :: {opi.ObjectName}";
                    break;
                case ObjectClass.ServerPrincipal:
                    throw new Exception("Not clear from documentation how to handle this. See: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-principal-permissions-transact-sql?view=sql-server-ver15");
                case ObjectClass.ServerRole:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-principal-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON SERVER ROLE :: {opi.ObjectName}";
                    break;
                case ObjectClass.Endpoint:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-endpoint-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON ENDPOINT :: {opi.ObjectName}";
                    break;
                case ObjectClass.Certificate:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-certificate-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ON CERTIFICATE :: {opi.ObjectName}";
                    break;
                case ObjectClass.FullTextStopList:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-full-text-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"FULLTEXT STOPLIST :: {opi.ObjectName}";
                    break;
                case ObjectClass.AsymmetricKey:
                    // https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-asymmetric-key-permissions-transact-sql?view=sql-server-ver15
                    permissionedObjectExpression = $"ASYMMETRIC KEY :: {opi.ObjectName}";
                    break;
                case ObjectClass.AvailabilityGroup:
                case ObjectClass.ExternalDataSource:
                case ObjectClass.ExternalFileFormat:
                default:
                    throw new Exception($"{opi.ObjectName} is in object class {opi.ObjectClass}, which is not a supported object class.");
            }

            var result =
                $"{opi.PermissionState.ToString().ToUpper()} {opi.PermissionType} {permissionedObjectExpression} TO {toTarget}";
            if (string.IsNullOrWhiteSpace(result))
                System.Diagnostics.Debugger.Break(); // <-- LOL.
            return result;
        }