Scripting object permissions independently

Không có tệp repository, test hoặc entry point nào được nêu tên; trước tiên hãy tìm ObjectPermissionInfo và scripting API hoặc permission API hiện có trong repository. So sánh cách xử lý object-class của SQL Server được đề xuất với hành vi hiện tại và tài liệu, sau đó xác định các trường hợp được hỗ trợ và các test trước khi quyết định liệu việc chuyển đổi đã hoàn tất hay chưa.

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