views:

267

answers:

1

I see from BOL that you can apply permissions to a T-SQL synonym, but in playing around with synonyms I'm not clear when you would need to do that if you have already GRANTed permissions to the base object.

eg. If I have a synonym in database A that points to a table FRED in database B, then it appears that as long as user Joe is granted SELECT on [Fred].[B] then Joe can do SELECT * FROM [Fred].[B].

+1  A: 

Using Synonyms (Database Engine)

The following permission statements are associated only with the synonym and not the base object: (then mentions GRANT, REVOKE; DENY)

After that, ownership chaining applies.

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

CREATE SYNONYM dbo.FooBar FOR dbo.MyBaseProc
GO
GRANT EXECUTE ON dbo.FooBar TO MyUser
GO
REVOKE EXECUTE ON dbo.MyBaseProc TO MyUser
GO
EXEC AS USER = 'MyUser'
GO
PRINT '1'
EXEC dbo.MyBaseProc --fail
GO
PRINT '2'
EXEC dbo.bob    --pass
GO
REVERT
GO


DENY EXECUTE ON dbo.MyBaseProc TO MyUser
GO
PRINT '3'
EXEC AS USER = 'MyUser'
GO
EXEC dbo.bob    --pass, 'coz DENY aint checked...
GO
REVERT
GO

Edit: I hope I've answered your question...

gbn