Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Implementing RBAC Expand / Collapse
Posted Friday, March 22, 2013 10:48 PM



Group: General Forum Members
Last Login: Monday, November 21, 2016 11:03 AM
Points: 20,014, Visits: 18,255
Steve Jones - SSC Editor (3/22/2013)
Are you saying you want a read only and a read/write role? Separate from db_Datereader/writer?

That's easy to script.
loop through all tables in all databases, grant rights to a standard named role (MyReadRole).
Add users to the role.

However if you want something that's not a pattern, you have to do it manually.

Agreed on the manual aspect for the roles that don't follow a pattern.

If a decision is made to grant access via stored procedures, the manual labor becomes a little easier.

But I have to wonder - if you are looking to recreate db_datareader, why?

Jason AKA CirqueDeSQLeil
I have given a name to my pain...


Posting Performance Based Questions - Gail Shaw
Post #1434567
Posted Thursday, May 15, 2014 8:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2016 2:40 AM
Points: 49, Visits: 168
If you want to comply with the principle of Least Privilege, then you should start by talking with business.
I usually take the time to talk with business about their business roles, and what they do.
This I use to make a logical model, and when we agree on the functionality and the principles then I can make a physical model.
It is very important if you work in a high-security organisation to know the demands of security and audit.
With a physical model, that is acceptepted by business, I can implement roles by AD-groups and user defined database- or server-roles. I do not use the default roles, as they do not comply with the principle of Least Privilege.
The roles I usually named by their function, which helps business, operations and service disk in the daily administration.
This is a huge task, but you will get new and unique knowledge about the business. In the long run your work will pay off.

/Niels Grove-Rasmussen
Post #1571344
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse