March 18, 2019 at 7:12 am
HI,
I'm building SQL Server instance for reporting purposes. My plan is to use AD groups for server and database logins. I have an AD group Database_Reader which has firewall authentication to Server A. I add other AD groups to Database_Reader so that I don't need to send separate firewalls authentication requests .
Now, the problem is all people in the Database_Reader has access to all databases of SERVER A.
I want to make sure each different AD group under Database_Reader only has access to the databases they should have access to . How do I achieve this without the need to submit firewall authentication for each group separately and still grant them access to the correct database/tables ?
Thanks,
PSB
March 18, 2019 at 8:22 am
Have multiple AD Groups in SQL with the correct default database and with the correct permissions to the various databases.
I do not understand the firewall part as these are authenticated against the domain controller. But you could have one main group that allows connect to the server only, with sub groups with a subset of the users used to provide more advanced permissions to your users and connect them to the correct database
March 18, 2019 at 9:18 am
PSB - Monday, March 18, 2019 7:12 AMHI,I'm building SQL Server instance for reporting purposes. My plan is to use AD groups for server and database logins. I have an AD group Database_Reader which has firewall authentication to Server A. I add other AD groups to Database_Reader so that I don't need to send separate firewalls authentication requests .
Now, the problem is all people in the Database_Reader has access to all databases of SERVER A.
I want to make sure each different AD group under Database_Reader only has access to the databases they should have access to . How do I achieve this without the need to submit firewall authentication for each group separately and still grant them access to the correct database/tables ?Thanks,
PSB
If you have added database_readers as a LOGIN to the SQL Server, they will be able to see all of the databases, but they will not be able to actually open any database. You will need to assign them rights in each database.
Are these users logging directly into the server? If so, that's likely not a good practice.
If you have only added them to the local group on the server, and not created the login in SQL or granted rights to the databases,then the permissions are likely far more than they should be.
For example, if a local server group (like administrators) has been granted sysadmin access to the SQL Server, and you have added the database_readers group to this server group, then they will also be sysadmins.
Can you provide more detail?
Does the firewall rule simply allow the users to connect to the server from their desktops, or does it allow them to RDP to the server?
Are they connecting using SSMS from their local machines, or RDP?
What groups are defined as sysadmins on the SQL Server? Do these groups contain the database_readers group?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 18, 2019 at 9:29 am
Current security :
AD Group : Database_Reader
Members : AD Group 1
AD Group 2
Individual User 1
Individual User 2
I grant read access to Database_Reader as a whole . So all groups/users are getting access to entire databases and it's objects .
New Plan : Change Individual Users to AD level groups wherever possible . Then create SQL Logins for each AD Group ( Group 1,2 and 3) .Tehn create mapped sql user for each AD group and assign necessary permissions .
AD Group : Database_Reader
Members : AD Group 1
AD Group 2
AD Group 3
--Using code below
USE [DBNAME];
GO
CREATE USER [DIR\prd-db] FROM LOGIN [DIR\prd-db];
GO
CREATE ROLE Support_Team;
GO
EXEC sp_addrolemember 'Support_Team', 'DIR\prd-db';
GO
GRANT SELECT, INSERT,UPDATE ON [dbo].[TableA] TO Support_Team;
GO
GRANT SELECT, INSERT,UPDATE ON [dbo].[TableB] TO Support_Team;
GO
Let me know if this will work .
Thanks,
PSB
March 18, 2019 at 9:32 am
Does the firewall rule simply allow the users to connect to the server from their desktops, or does it allow them to RDP to the server? - Firewall rule allows to just connect to SQL SERVER
Are they connecting using SSMS from their local machines, or RDP? -- Local machine
What groups are defined as sysadmins on the SQL Server? Do these groups contain the database_readers group? - Sys admins are in a different group .
Thanks,
PSB
March 18, 2019 at 9:57 am
PSB - Monday, March 18, 2019 9:29 AMCurrent security :AD Group : Database_Reader
Members : AD Group 1
AD Group 2
Individual User 1
Individual User 2I grant read access to Database_Reader as a whole . So all groups/users are getting access to entire databases and it's objects .
New Plan : Change Individual Users to AD level groups wherever possible . Then create SQL Logins for each AD Group ( Group 1,2 and 3) .Tehn create mapped sql user for each AD group and assign necessary permissions .
AD Group : Database_Reader
Members : AD Group 1
AD Group 2
AD Group 3--Using code below
USE [DBNAME];
GO
CREATE USER [DIR\prd-db] FROM LOGIN [DIR\prd-db];
GO
CREATE ROLE Support_Team;
GO
EXEC sp_addrolemember 'Support_Team', 'DIR\prd-db';
GO
GRANT SELECT, INSERT,UPDATE ON [dbo].[TableA] TO Support_Team;
GO
GRANT SELECT, INSERT,UPDATE ON [dbo].[TableB] TO Support_Team;
GO
Let me know if this will work .
Thanks,
PSB
You are either confused, or are using the wrong terms.
You said:
Then create SQL Logins for each AD Group ( Group 1,2 and 3) .Tehn create mapped sql user for each AD group and assign necessary
But, these are not SQL logins. They are windows logins.
You also said:
I grant read access to Database_Reader as a whole . So all groups/users are getting access to entire databases and it's objects .
Huh? If you have created this group as a LOGIN to the server, then you must have granted this group rights to each database.
Your description also does not match your code.
You talk about an AD group named "Database_Reader". But your code is referring to the group DIR\prd-db. Which is it?
Taking your code, and attempting to make sense of your description, this is what I think you are trying to do:
1. Add the AD group 'Database_readers' as a login to SQL Server.
2. Create a role in each databases. Support_Team looks like the one you created.
3. You can grant the ROLE permissions to the schemas not the individual objects. That may be easier to manage. But you keep talking about read only, and the script grants insert and update.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 18, 2019 at 12:44 pm
To make things simpler - don't use the group 'Database_Reader' that is utilized for firewall access as a login to SQL Server. Remove the individual accounts from this group and move them to an appropriate security group where possible.
In SQL Server - add the groups (and individuals) as logins and grant those groups access to the specific databases they require.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 18, 2019 at 1:04 pm
ditto
March 18, 2019 at 1:25 pm
Taking your code, and attempting to make sense of your description, this is what I think you are trying to do:
1. Add the AD group 'Database_readers' as a login to SQL Server. -- This AD group is used to gain access to the server level only. For individual database access I am creating logins for every AD group who are subset of Database_Reader.
2. Create a role in each databases. Support_Team looks like the one you created. --- This is what my new plan is . Support_Team role for Database A for prd-db AD group who is a subset of Database_Reader
3. You can grant the ROLE permissions to the schemas not the individual objects. That may be easier to manage. But you keep talking about read only, and the script grants insert and update.-- Some AD group will need just read and some other liks prd-db will need read/write . I just provided a sample. For large databases , I will be granting permissions to the schemas .
Thanks,
PSB
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply