July 6, 2010 at 10:57 am
We are coming from a single tennant system and merging database servers to a multi tennant system to control costs, however that gives the tennants access to each other's databases.
How do I setup security for groups on my databases so that:
Admin can access all DB's (as it seems to be now, i just don't want to break this trying to secure things).
Users in Group A can only read/write/alter database A and no others.
Users in Group B can only read/write/alter database B and no others.
Also, how do I do this so that future DB's get created with proper permissions?
I think I know how to do this manually with deny and so forth, but as I envision that, it gets ugly as groups/db's get added in future. I presume I am seeing it wrong and making it harder than it needs to be....
Thanks,
Matt
July 6, 2010 at 11:08 am
SQL Server uses a role based security model. Your databases get created already without granting access to users on the server. You should set up database roles with whatever permissions you wish, then make your users members of those roles as required.
You should however make sure that users on a server do not have admin priviledges at the OS level, or they will get access to all databases on the server.
July 6, 2010 at 11:17 am
Steve Cullen (7/6/2010)
SQL Server uses a role based security model. Your databases get created already without granting access to users on the server. You should set up database roles with whatever permissions you wish, then make your users members of those roles as required.
Ok I think this is where I am fuzzy, on how to set up these roles properly to do this.
So I would create roles at the server level like:
AccessDB-A
AccessDB-B
(I'd use less stupid names on server, just want to keep this simple and clear.)
Then apply the AccessDB-A role on the Group A login group?
You should however make sure that users on a server do not have admin priviledges at the OS level, or they will get access to all databases on the server.
They do not, and any of my users who do give them such should be flogged.
July 6, 2010 at 12:33 pm
Are you working with SQL Logins or Windows accounts ?
For Windows accounts, I create Active Directory Groups, such as "DatabaseA_ReadWrite", then put Windows accounts such as MyDomain\MikeSmith into that group. Then give the A/D group "DatabaseA_ReadWrite" database permissions to Database A.
July 6, 2010 at 12:39 pm
Windows AD Groups
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply