Scripting object permissions independently

Aucun fichier du dépôt, test ou point d’entrée n’est indiqué ; commencez par localiser ObjectPermissionInfo et l’API de scripting ou d’autorisations existante dans le dépôt. Comparez la gestion proposée des classes d’objets SQL Server au comportement actuel et à la documentation, puis définissez les cas pris en charge et les tests avant de décider si la conversion est complète.

@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;
        }