How do I script Database-Level Permissions?

[jzabroski]

I'll offer to add this to the sample docs on Microsoft Docs if I get an answer to this.

How do I use SMO to script out permissions like GRANT CONNECT or DENY CONNECT that are database-level permissions?

For example, in SSMS, I can do the following:

1. Open Object Explorer pane
2. Click Connect -> Database Engine... and connect to a database server
3. Expand Databases under that server
4. Right click on a user database (e.g., Logging_DEV in this screenshot below), select Properties from the context menu
5. In the Select a page box, click Permissions. Then click public.
6. Scroll down to see that public has "View any column master key definition" and "View any column encryption key definition".
7. If I uncheck the grant checkboxes, and go to Script --> Script Action to New Query Window, the following script gets generated (but I don't know what API call SSMS is making to SMO to do this):

use [Logging_DEV]
GO
REVOKE VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [public] AS [dbo]
GO
use [Logging_DEV]
GO
REVOKE VIEW ANY COLUMN MASTER KEY DEFINITION TO [public] AS [dbo]
GO

Below is my attempt to reproduce this logic using SMO:

namespace Simmaculate.Tools.Database.Cli.Scripters
{
    public class ScriptResult
    {
        public string Name { get; set; }
        public string Script { get; set; }
    }
}

using System.Collections.Generic;

namespace Simmaculate.Tools.Database.Cli.Scripters
{
    interface IScripter
    {
        IEnumerable<ScriptResult> Generate();
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace Simmaculate.Tools.Database.Cli.Scripters.ObjectPermissionsScripters
{
    public class DatabaseLevelPermissionsScripter : IScripter
    {
        private readonly Server _server;
        private readonly Microsoft.SqlServer.Management.Smo.Database _database;
        private readonly ScriptingOptions _scriptingOptions;

        public DatabaseLevelPermissionsScripter(
            Server server,
            Microsoft.SqlServer.Management.Smo.Database database,
            ScriptingOptions scriptingOptions)
        {
            _server = server ?? throw new ArgumentNullException(nameof(server));
            _database = database ?? throw new ArgumentNullException(nameof(database));
            _scriptingOptions = scriptingOptions ?? throw new ArgumentNullException(nameof(scriptingOptions));
        }

        public IEnumerable<ScriptResult> Generate()
        {
            var tmpScriptingOptions = ScripterHelper.CopyScriptingOptions(_scriptingOptions);
            tmpScriptingOptions.Permissions = true;
            // If PrimaryObject is not true, it doesn't matter if Permissions is true, nothing will be scripted out.
            // We have to script out the Permissions as dependencies rather than first-class objects, and then extract them.
            tmpScriptingOptions.PrimaryObject = true;

            foreach (DatabasePermissionInfo databaseObject in _database.EnumDatabasePermissions())
            {
                yield return new ScriptResult() {
                    Name = $"{databaseObject.ObjectSchema}.{databaseObject.ObjectName}",
                    // todo: I can't seem to generate a script from a DatabasePermissionInfo object
                    // In particular, databaseObject.PermissionType.ToString() prints an error message:
                    // "ViewAnyColumnEncryptionKeyDefinition doesn't have a PermissionName attribute defined"
                    Script = string.Empty 
                };
            }
        }
    }
}

[shueybubbles]

I think you have to script the Database object and set ScriptingPreferences.IncludeScripts.Permissions to true

[jzabroski]

Hi, if I understand your guidance correctly, you are suggesting the following code, but it includes a lot of extra stuff and there doesn't seem to be a way to just script the database-level permissions.

Here is my sample code:

public class DatabasePermissionsScripter : IScripter
    {
        private readonly Microsoft.SqlServer.Management.Smo.Database _database;

        public DatabasePermissionsScripter(Microsoft.SqlServer.Management.Smo.Database database)
        {
            _database = database ?? throw new ArgumentNullException(nameof(database));
        }
        public IEnumerable<ScriptResult> Generate()
        {
            var transfer = new Microsoft.SqlServer.Management.Smo.Transfer(_database)
            {
                Options =
                {
                    ScriptBatchTerminator = true,
                    ToFileOnly = false, // was true
                    SchemaQualify = false,
                    ScriptSchema = true,
                    Permissions = true,
                    IncludeDatabaseRoleMemberships = true,
                    NoCollation = true,
                    DriAll = false, // DRI = Declarative Referential Integrity
                    FullTextCatalogs = false,
                    FullTextIndexes = false,
                    Indexes = false,
                    ClusteredIndexes = false,
                    NonClusteredIndexes = false,
                    Triggers = false,
                    PrimaryObject = false,
                    WithDependencies = false,
                    EnforceScriptingOptions = true,
                    //FileName = @"$($OutputDirectory)/($databaseName).sql"
                }
            };

            foreach (var script in transfer.EnumScriptTransfer())
            {
                yield return new ScriptResult()
                {
                    Name = "DatabaseSchema",
                    Script = script
                };
            }
        }
    }

The behavior I observe from this output is:

CREATE USER [Foo]
GO
CREATE ROLE [Fighters]
GO
ALTER ROLE [Fighters] ADD MEMBER [Foo]
GO
CREATE SCHEMA [OneByOne]
GO
CREATE TYPE [OneByOne].[TimesLikeThese] AS TABLE(
	[OneWayMotorWay] [BOOL] NOT NULL,
	[OneThatDrivesAway] [BOOL] NOT NULL,
	[FollowsYouBackHome] [BOOL] NOT NULL,
	[StreetLightShining] [BOOL] NOT NULL,
	[WildLightBlindingBright] [BOOL] NOT NULL,
	[Burning] [BOOL] NOT NULL,
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [OneByOne].[Chorus]
RETURNS @TimesLikeThese TABLE (
	[Refrain] NVARCHAR(MAX) NOT NULL
)
AS
BEGIN
	INSERT INTO @TimesLikeThese
	SELECT N'It's times like these you learn to live again' UNION ALL
	SELECT N'It's times like these you give and give again' UNION ALL
	SELECT N'It's times like these you learn to love again' UNION ALL
	SELECT N'It's times like these time and time again';
	RETURN;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GreatestHitHistory](
	[GreatestHitId] [bigint] NOT NULL,
	[SongName] [datetime] NOT NULL,
	[IsGrammyNominated] [bit] NOT NULL,
	[Created_Date] [datetime] NULL,
	[Modified_Date] [datetime] NULL,
	[Version] [timestamp] NOT NULL,
	[ValidFromInUTC] [datetime2](7) NOT NULL,
	[ValidToInUTC] [datetime2](7) NOT NULL
) ON [PRIMARY]

GO
CREATE CLUSTERED INDEX [IX_GreatestHitHistory] ON [dbo].[GreatestHitHistory]
(
	[ValidToInUTC] ASC,
	[ValidFromInUTC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GreatestHit](
	[GreatestHitId] [bigint] NOT NULL,
	[SongName] [datetime] NOT NULL,
	[IsGrammyNominated] [bit] NOT NULL,
	[Created_Date] [datetime] NULL,
	[Modified_Date] [datetime] NULL,
	[Version] [timestamp] NOT NULL,
	[ValidFromInUTC] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[ValidToInUTC] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_GreatestHit] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_GreatestHit_SongName] UNIQUE NONCLUSTERED 
(
	[SongName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
	PERIOD FOR SYSTEM_TIME ([ValidFromInUTC], [ValidToInUTC])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[GreatestHitHistory] )
)
GO

[shueybubbles]

A Permission is not a named or distinctly identifiable object, IE it doesn't have a URN. The SMO scripting objects like Scripter are meant to script based on a URN.
This functionality would have to be a new feature and probably be a method on the SqlSmoObject to script permissions on arbitrary objects.

[jzabroski]

Understood - but how does SSMS do it if they use SMO? Are they not using SMO for this scenario?

[shueybubbles]

I haven't looked at the code but a common pattern in SSMS is to set the executionmode on the ServerConnection to CaptureSql and call Alter on the object. Sent from my T-Mobile 5G Device Get Outlook for Android<https://aka.ms/ghei36>

…

________________________________ From: John Zabroski <[email protected]> Sent: Tuesday, January 19, 2021 12:39:47 PM To: microsoft/sqlmanagementobjects <[email protected]> Cc: David Shiflet <[email protected]>; Comment <[email protected]> Subject: Re: [microsoft/sqlmanagementobjects] How do I script Database-Level Permissions? (#43) Understood - but how does SSMS do it if they use SMO? Are they not using SMO for this scenario? — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fsqlmanagementobjects%2Fdiscussions%2F43%23discussioncomment-293873&data=04%7C01%7C%7C7a125397150242251e7908d8bca137c6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637466747893465817%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=biJLjceHDTRwfj3CPJNNI5Vr%2BGcEWLD8zLorBEC6AQY%3D&reserved=0>, or unsubscribe<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAAQ7GCTRPSGRXAE4HEGXBYLS2W7WHANCNFSM4VTLMZNQ&data=04%7C01%7C%7C7a125397150242251e7908d8bca137c6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637466747893475803%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=hLJd4RfgNYA5vvD3SLQNlnbrNUL74HW%2FjMz9KCLtd4Y%3D&reserved=0>.

[jzabroski]

Thank you. Got it:

ServerConnection.SqlExecutionModes

ServerConnection.CapturedSql, inherited from ConnectionManager.CapturedSql