SqlPackage generates GRANT TO NULL permission scripts in some cases thus producing permanent diff
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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