SQL Server Permissions to synonym and underlying objects

I have DatabaseX_2024 and DatabaseY on same SQL Server. My app works with DatabaseY but need some data from DatabaseX_2024. Because name of DatabaseX_2024 is changing (will be DatabaseX_2025 next year), I made synonym in DatabaseY. When name of DatabaseX_2024 changes, I have to recreate synonym in DatabaseY and change name of targeting DB to DatabaseX_2025, and my app will works without changes within. So far, so good.

What I found is that I have to grant permissions for users in DatabaseY not only for synonym (in same DB), but also on underlying objects in DatabaseX_2024. Not ideal solution, but if it have to be like that, let it be.

Problem (or strange behaviour) is this:
If I pick existing user from DatabaseX_2024 and add them to DatabaseY and grant him permission to synonym, everything works as expected. But if I create new user in DatabaseY, grant him permission to synonym and after that add him to DatabaseX_2024 and grant him permissions to underlying objects in that DB, that new user can NOT use synonym because DatabaseX_2024 refuses it (“does not have permission”!?) unless that new user is owner of schema to which synonym belongs in DatabaseY!?!?

As I can have only one owner of schema, it means that I can NOT add multiple users in DatabaseY which will use synonym!?

Where is the problem and what is solution for this?

NOTE: SQL Server uses Integrated Security (domain usernames).

I set that new user as owner of schema, but there must be better solution…

Honestly, the real problem is your design here. I strongly urge you to rethink your decision here to create a new database each year. The details of the year should simply be a column in your data, nothing more. Creating a new database every year is not going to scale, and (as you are experiencing) causes problems for even “simple” things like permissions.

If you fix your design, and have a single database to refer to then there’s no changing of synonyms every year, and no needing to apply permissions every year. This is the simple solution (even if there is a small amount of “hurt” fixing your design).

I want to address your claim about needing to own the schema; I can’t replicate this.:

USE master;
GO
--Create a test Login
CREATE LOGIN TestLogin WITH PASSWORD = 'Test123', CHECK_POLICY = OFF;
GO
--Create test databases
CREATE DATABASE SecurityTest;
GO
CREATE DATABASE SecurityTest2;
GO
--Create a sample object in the first database
USE SecurityTest;
GO
CREATE TABLE dbo.MyTable (ID int);
GO
INSERT INTO dbo.MyTable (ID)
VALUES(1);
GO
--Grant the LOGIN access to the database and table
CREATE USER TestUser FOR LOGIN TestLogin;
GO
GRANT SELECT ON dbo.MyTable TO TestUser;
GO
--Create a sample synonym in the second database
USE SecurityTest2;
GO
CREATE SYNONYM dbo.MySynonym FOR SecurityTest.dbo.MyTable;
GO
--Grant the LOGIN access to the database and synonym
CREATE USER TestUser FOR LOGIN TestLogin;
GO
GRANT SELECT ON dbo.MySynonym TO TestUser;
GO
--Impersonate the LOGIN and query the SYNONYM
EXECUTE AS LOGIN = N'TestLogin';
GO
SELECT DB_NAME(), ID
FROM dbo.MySynonym;
GO
REVERT;
GO
--Clean up

USE master;
GO
ALTER DATABASE SecurityTest2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SecurityTest2;
GO
ALTER DATABASE SecurityTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SecurityTest;
GO
DROP LOGIN TestLogin;
GO

This works without issue; the LOGIN was able to query the table dbo.MyTable in the database SecurityTest via the SYNONYM dbo.MySynonym in the database SecurityTest2. Ownership of the schema is defintely not required, and I would suggest that if you need to apply those permissions, you have done something wrong; what that is we lack details of.

As for a workaround, as I mentioned in my comment, there are couple, such as database chaining, and signed objects. You cannot, however, sign SYNONYMs. You can use database chaining, and I’ll demonstrate that below, however, this comes with a wealth of security considerations, such as a user with permissions to CREATE objects in one database will have the ability to elevate their permissions in the other database by creation of objects in their database even though they don’t have access to the objects in the other database.

That being said, here is an overly simplified example of how this works. You may wish to use a different owner for the databases than SA (where I do use it, I use a disabled LOGIN for the databases that need chaining). You also need to still create a USER for the LOGIN in the other database, however, you’ll notice that I don’t need to give it any explicit permissions; public is enough:

USE master;
GO
--Create a couple of test databases
CREATE DATABASE Chain1 WITH DB_CHAINING ON;
CREATE DATABASE Chain2 WITH DB_CHAINING ON;
GO
--Change the owner of the database (to the sa LOGIN)
ALTER AUTHORIZATION ON DATABASE::Chain1 TO sa;
ALTER AUTHORIZATION ON DATABASE::Chain2 TO sa;
GO
--Create a test LOGIN
CREATE LOGIN Chainer WITH PASSWORD = '123abc', CHECK_POLICY = OFF;
GO
USE Chain1;
GO
--Create a USER and table in the first database. Note no permissions given.
CREATE USER Chainer FOR LOGIN Chainer;
GO
CREATE TABLE dbo.ChainTable (ID int IDENTITY);
GO
INSERT dbo.ChainTable
DEFAULT VALUES;
GO
USE Chain2;
GO
--Create a USER and SYNONYM in the second database. Give permissions here,
CREATE USER Chainer FOR LOGIN Chainer;
GO
CREATE SYNONYM dbo.ChainTable FOR Chain1.dbo.ChainTable;
GO
GRANT SELECT ON dbo.ChainTable TO Chainer;
GO
--Impersonate the LOGIN and query the SYNONYM
EXECUTE AS LOGIN = 'Chainer';
GO
SELECT *
FROM dbo.ChainTable;
GO
REVERT;
GO

--Clean up
USE master;
GO
ALTER DATABASE Chain1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE Chain1;
GO
ALTER DATABASE Chain2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE Chain2;
GO
DROP LOGIN Chainer;
GO

Filed under: Kiến thức lập trình - @ 22:31

Thẻ: sql-serverpermissionsschema

Thiết kế website giá rẻ

Danh mục

SQL Server Permissions to synonym and underlying objects