Scripting object permissions independently

[jzabroski]

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

[shueybubbles]

thx for looking at it. A few things pop up:

1. Throw something more specific than "Exception". Either a standard .Net exception like ArgumentException or InvalidOperationException, or a SmoException.
2. Exception messages need to be defined a localizable resources in the strings file with other exception messages
3. Object names need to be properly escaped to handle names with ] in them. There are common helper functions to encode object names in script snippets.

Where is this function going to be used?

[jzabroski]

Where is this function going to be used?

I use it to dump permissions to a file per role, so that permissions are independently managed from objects. It makes it easier to audit who-has-access-to-what rather than going to the bottom of every file-per-object to see each object's permissions.

In general, I find this a lot more useful that the server scripting support available today.

[shueybubbles]

Can you add the wrapper method that walks the various object hierarchies and dumps the permission scripts generated from this method too? It could be a method on Server.

[jzabroski]

Can you add the wrapper method that walks the various object hierarchies and dumps the permission scripts generated from this method too? It could be a method on Server.

Yes.

Honestly, the way I manage security is extremely clean - there is only one thing bad with what I do in my whole process, which is that login tokens don't expire until the session ends, and the only way to force an update to a login token is to end the user's session. For AD Groups, I wish SQL Server had a refresh tokens stored proc call or a way to forcefully invalidate login tokens.