To remove an explicit right you need to remove the row from DBC.AccessRights. This is done using a REVOKE statement.
To use implicit rights means that you have to set up your hierarchy as required. This is typically done using the GIVE statement for existing users and databases.
HOWEVER...I have to be honest and say that I've never heard of this requirement before (doesn't mean it isn't valid) and I can't think of a reason for doing it.
What are you actually trying to achieve here?
Previously someone has granted direct access on many databases to users, I want to change and grant those rights with the help of Roles.
That makes sense (I got confused by the terminology that you used. Access rights granted to Roles are not usually referred to as 'implicit', they are a form of explicit rights).
Using roles is very simple.
- create a Role
- grant access rights to it
- grant the role to the user and maybe make it the user's default role.
The bit that takes time is working out which roles you need and which users should have a role granted to them.
On the system in question, you probably need to do some analysis of the existing access rights that are in place.
Work out which access rights are commonly granted together. Those access rights go into a single role which is then granted to the relevant users.
My strongest advice is to keep it simple. Only make things complicated when you need to.
Assuming this is a production system you can often get started with:
- 1 role for ETL userid's
- 1 role for DBA's
- 1 role for end users (possibly 1 role per application if they have very different security requirements)
- 1 role for backup userid's
How does that sound?
So it sounds like you possibly need a role per project.
If it is possible for a user to need multiple roles then you need to think about how the user will identify which role they should be using. Also, what tool(s) are the users using?
Assume have 3 projects (P1, P2 and P3) and so you create three roles (R_P1, R_P2, and R_P3 ).
A user needs access to the database for all three projects, so you grant all three roles to the user.
What mechanism does the user have to change their current role?
- If they are using Studio, SQLA, TPT etc. then they can simple code a 'SET ROLE' command.
- If however they are using a tool like Cognos, MSI etc. then AFAIK they don't have a capability for just executing adhoc sql. In this case you now have to give them a default role, which might have to be 'ALL' or you could use a nested role scenario and make the relevant top-level role their default.
Neither approach is right or wrong, but this side needs to be considered when designing your roles.
Easiest way is to use the output from one of my AccessRights reporting macros - or at least use the code within that as the basis for what you want to do.
These macros are available here.