May 19, 2016 at 6:07 am
Hi,
I have two databases in same server.First data base some users already created.Second data base i created some synonyms related to first database.Now my concern is how can i give Grant permissions to that particular existed users for second database created synonyms.
Can any one help me out on this.
May 19, 2016 at 6:12 am
Pretty much the same way you grant permissions to objects in a database.
GRANT <permission> ON <synonym> TO <login>
But why would you need synonyms on the same server? It's easy enough for users just to use Database.Schema.Table conventions in their code, isn't it?
May 19, 2016 at 6:17 am
i think synonyms don't magically solve cross database chaining.
if User [Bob] has access to [db1], but a synonym in there points to [db2], you need to add [bob] to be in a role that has permissions in [db2] as well;
i'd start with a read only role, but expand it if you are actually updating the remote tables pointed at by the synonym.
Lowell
May 19, 2016 at 6:22 am
dudekula.kareemulla, Lowell said more plainly what I was thinking but didn't say.
Lowell (5/19/2016)
i think synonyms don't magically solve cross database chaining.
Synonym or no synonym, you have to give the users permission in the second database. So that's why I'm confused why you're even bothering with them.
May 19, 2016 at 6:56 am
Is there any possibility to give select privilege permission with out creating same user in another database??
Problem is i do not want create same user in another database in same server for some security reasons.
May 19, 2016 at 7:08 am
dudekula.kareemulla (5/19/2016)
Is there any possibility to give select privilege permission with out creating same user in another database??Problem is i do not want create same user in another database in same server for some security reasons.
nope, not possible.
it is not possible to grant permissions to an object, well, without granting privileges to the object.
what you can do, however, is use the principal of least privilege, and grant extremely limited permissions.
it sounds like you typically grant way to many permissions, and need to resolve that type of logic. hope this example helps.
say for example, you have a synonym named [RemoteTable] in the [Production] database pointing to OtherDatabase.dbo.ImportantTable
create a role and add the right users to that role like this:
USE OtherDatabase;
GO
CREATE ROLE LimitedPermissionsForSynonym;
GRANT SELECT ON dbo.ImportantTable TO LimitedPermissionsForSynonym;
CREATE USER [mydomain\Bob] FOR Login [mydomain\Bob]
EXEC sp_addrolemember N'LimitedPermissionsForSynonym', N'mydomain\Bob'
now test it :
USE Production;
GO
--test as specific login
EXECUTE AS login = 'mydomian\bob'
--test the synonym
SELECT * FROM [RemoteTable] --works
--test the underlying object
SELECT * FROM OtherDatabase.dbo.ImportantTable --works
--test OTHER tables you know exist, but did not grant
SELECT * FROM OtherDatabase.dbo.HumanResourcesPayroll --FAILS
--turn back into myself.
REVERT;
Lowell
May 19, 2016 at 8:59 am
You could grant those individual permissions to an account, but it is usually considered far easier to maintain such things by creating a role and then adding the users or the user group to the role (like Lowell did).
The only way around granting the users permission to the second database is to use a LinkedServer that uses a SQL Login with those permissions on the second database. But that's a workaround that sometimes causes more trouble than it's worth. It makes sense to use such a thing between servers, not so much to use it between databases.
It can be done, however, if you feel strongly about not giving your users access to database 2.
March 14, 2018 at 11:40 pm
As long as the owner for the both the databases is same, you only need db_reader in the database where synonyms are created alongwith just 'public' role for the user in which the actual tables are.
eg:
DB1 with tables
DB2 with Synonyms
Login: xyz
User: xyz
Give db_reader to user xyz on DB2 database and just give public to the user in DB1 database.
By doing this, you dont need to explicitly grant the access on each and every object.
P.S. I have tested this for 'sa' being the owner for the both the databases.
Thanks
March 15, 2018 at 4:57 am
Salil-386686 - Wednesday, March 14, 2018 11:40 PMAs long as the owner for the both the databases is same, you only need db_reader in the database where synonyms are created alongwith just 'public' role for the user in which the actual tables are.
eg:
DB1 with tables
DB2 with Synonyms
Login: xyz
User: xyzGive db_reader to user xyz on DB2 database and just give public to the user in DB1 database.
By doing this, you dont need to explicitly grant the access on each and every object.P.S. I have tested this for 'sa' being the owner for the both the databases.
Thanks
I'm not sure how granting public on DB1 allows a user to do their work in DB1 while referencing the synonyms that apply to DB2. It seems to me that you're working in the opposite direction of the original question.
Granting db_datareader in DB2 doesn't resolve the initial issue either. At that point, a user can just read from the database without requiring the synonyms.
December 14, 2021 at 12:39 am
But why would you need synonyms on the same server? It's easy enough for users just to use Database.Schema.Table conventions in their code, isn't it?
You'll just love all the places you have to change code if they rename the database or move it to another instance. 😀
For that reason alone (which has happened a whole lot for me), I don't allow more or less than the 2 part naming convention in code. Synonyms and "pass-thru" views are definitely the way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply