Sql-server – Create Synonym error for a user-SQL Server

permissionsschemasql serversynonyms

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.

Best Answer

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."

Related Solutions

Sql-server – restore the defult SQL Server Public Role

Well, this would be the script to restore the connection permissions to the defaults.

use [master]
GO
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [public] AS [sa]

If that doesn't work, try stopping SQL Server, starting it in single user mode ("sqlservr.exe -m" from an administrator command prompt), connecting with sqlcmd.exe (which will default to the local default instance with Windows authentication), and running the script that way.

Sql-server – User in multiple windows groups mapped to sql logins

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login. ~~And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2.~~ And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2

Best Answer

Related Solutions

Sql-server – restore the defult SQL Server Public Role

Sql-server – User in multiple windows groups mapped to sql logins

Related Question