Create Synonym error for a user-SQL Server

Question

I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission. When the user tried to create synonym as shown below.

USE [DEMODB] GO CREATE SYNONYM [dbo].[MYSYN] FOR [SERVER01].[DEMODB].[dbo].[CHILD] GO The user gets the below error message. The specified schema name "dbo" either does not exist or you do not have permission to use it.

I checked the login/user settings. And the login belongs to public server role and user is mapped to the database with default schema as [dbo]

And also create synonym permission is granted for that database for that user as mentioned earlier.

How can i fix this.

Answer 1

As noted in Microsoft's documentation for CREATE SCHEMA:

"To create a synonym in a given schema, a user must have CREATE SYNONYM permission and either own the schema or have ALTER SCHEMA permission."

Answer 2

A user requires not only CREATE SYNONYM statement permissions but also ALTER permissions on the schema in order to create a synonym. ALTER permission on a schema, especially dbo, should be granted with care to follow the security principle of least privilege. It would be better (and easier) if the user creates the synonym in their own schema or in a schema with an owner other than dbo so that the user is sandboxed into that schema for ALTER purposes.

If the minimally-privileged user must create the synonym in the dbo schema, consider encapsulating the CREATE SYNONYM DDL in a stored procedure and use module signing to elevate permissions. That way, a user with execute permissions on the proc can create synonyms in the dbo schema but is limited to the functionality performed by the stored procedure. Below is an example of this technique.

CREATE OR ALTER PROC dbo.CreateSynonym
      @SynonymSchemaName sysname
    , @SynonymName sysname
    , @SynonymForServerName sysname
    , @SynonymForDatabaseName sysname
    , @SynonymForSchemaName sysname
    , @SynonymForObjectName sysname
AS
DECLARE @CreateSynonymStatement nvarchar(MAX) = N'CREATE SYNONYM ' 
    + QUOTENAME(@SynonymSchemaName)
    + N'.'
    + QUOTENAME(@SynonymName)
    + N' FOR '
    + QUOTENAME(@SynonymForServerName)
    + N'.'
    + QUOTENAME(@SynonymForDatabaseName)
    + N'.'
    + QUOTENAME(@SynonymForSchemaName)
    + N'.'
    + QUOTENAME(@SynonymForObjectName)
    + N';'
EXEC sp_executesql @CreateSynonymStatement;
GO

--create certificate and sign proc
CREATE CERTIFICATE CreateSynonymCert
   ENCRYPTION BY PASSWORD = 'T3mP0@rypAsso0rd'
   WITH SUBJECT = 'For CREATE SYNONYM in dbo schema';
ADD SIGNATURE TO dbo.CreateSynonym BY CERTIFICATE CreateSynonymCert WITH PASSWORD = 'T3mP0@rypAsso0rd';

--private key is ephemoral for this purpose so we remove it
ALTER CERTIFICATE CreateSynonymCert REMOVE PRIVATE KEY;

--create a user from certificate with the needed permissions
CREATE USER CreateSynonymCertUser FROM CERTIFICATE CreateSynonymCert;
GRANT CREATE SYNONYM TO CreateSynonymCertUser;
GRANT ALTER ON SCHEMA::dbo TO CreateSynonymCertUser;

--We could grant execute directly to the user but a role is easier to manage
--when multiple users are involved and adheres to RBAC security principles.
CREATE ROLE CreateSynonymRole;
ALTER ROLE CreateSynonymRole ADD MEMBER YourUser;
GRANT EXECUTE ON dbo.CreateSynonym TO CreateSynonymRole;
GO

--example usage
EXECUTE AS USER = 'YourUser';
GO
EXEC dbo.CreateSynonym
      @SynonymSchemaName = N'dbo'
    , @SynonymName = N'MYSYN'
    , @SynonymForServerName  = N'SERVER01'
    , @SynonymForDatabaseName  = N'DEMODB'
    , @SynonymForSchemaName  = N'dbo'
    , @SynonymForObjectName  = N'CHILD';
GO
REVERT;
GO

Answer 3

How can i fix this

How you should fix it is to either

1) Connect with a higher-privilege user to create the synonym.

or

2) Provision a schema owned by the user and have them create the synonym there.

How you shouldn't fix this is to grant a user who "has data reader permission" ALTER on schema::dbo. It's a cardinal rule of SQL Server security that only a privileged user can create objects in other users' schemas, as it's tantamount to allowing them to impersonate the schema owner.