microsoft / microsoft/DacFx

SqlPackage generates GRANT TO NULL permission scripts in some cases thus producing permanent diff

Open
#593 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- SqlPackage Version: 162.5.57.1
- .NET Framework (Windows-only) or .NET Core:
- Environment (local platform and source/target platforms): windows 11, sqlserver 2019

**Steps to Reproduce:**

1. Create new SSDT project "database1" with given code:
```sql
CREATE TABLE dbo.foo (id INT NOT NULL PRIMARY KEY);
GO

GRANT SELECT ON OBJECT::dbo.foo TO [some\usr] AS dbo;
GO
```
2. Build it
3. Generate publish script using command
```cmd
SqlPackage.exe /Action:Script /OutputPath:.\out.sql /SourceFile:.\database1.dacpac /TargetConnectionString:"Server=;Initial catalog=database1;TrustServerCertificate=true;Integrated security=true"
```

Generated script

```sql
USE [$(DatabaseName)];
GO
PRINT N'Creating Table [dbo].[foo]...';
GO
CREATE TABLE [dbo].[foo] (
[id] INT NOT NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
);
GO
PRINT N'Creating Permission Permission...';
GO
GRANT SELECT
ON OBJECT::[dbo].[foo] TO NULL
AS [dbo];
GO
PRINT N'Update complete.';
GO
```

The problem is with this GRANT:

```sql
GRANT SELECT
ON OBJECT::[dbo].[foo] TO NULL
AS [dbo];
```

Note, if grantee is defined like `some\usr` then no error or warning is generated during build, no matter that login or user with specified name is missing in the project. And the mentioned granted permission becomes _persistent schema difference_. But if you change grantee name to `some_usr` then build fails with:
> SQL71501: Permission: Permission has an unresolved reference to object [some_usr]

IMHO build should either fail with "unresolved reference to login/user" and/or publish script should contain GRANT with login/user name _exactly as it was defined_ in source code.

Additional context:
We don't store users and logins in SSDT project sources, in most cases we use Roles to manage permissions. Users, logins, role membership are maintained by DBA and other specialists. When generating publish scripts or discrepancy reports we always exclude object types Users, and Logins. But some old projects still have permissions addressed to specific user or login and sqlpackage keeps generating such GRANT TO NULL statements. Which, once again, become permanent diff.

**Did this occur in prior versions? If not - which version(s) did it work in?**

It worked similarly.

(DacFx/SqlPackage/SSMS/Azure Data Studio)

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the SSDT project and SqlPackage /Action:Script command described in the issue, then inspect how the permission grantee is resolved when its name contains a backslash. Compare the generated GRANT statement with the source declaration and verify that unresolved grantees either produce a build error or preserve the defined name without creating a persistent schema difference.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases, tooling
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.