9 times out of 10, it happens that I get a request that I have to create a new user and give him rights of any existing user.
My question is that how can we get the rights of that existing user?
I understand that rights can be given to a user by 2 methods:
Is there any query or step by step process I can follow so that I might get the rights of the existing user and give those rights to the newly created user?
Classify users into groups and assign Roles.
Roles are used to manage access rights. Define roles for user Groups like: Developers, Business Users, Power Users, ETL Batch IDs, DBAs etc.
Each role can have different object level access.
Sachin, everything has already been implemented.
What I want to see is to how I can get the roles of an already existing user.
Let me explain, I have to create a user 'xyz' and give him rights of an already existing user 'abc', how am I able to do this?
I think, if I can see what roles 'abc' has then I can give the same roles to the newly created 'xyz'.
If 'abc' has any direct rights then I must also give those direct rights to 'xyz'.
My question is still the same, how can I get the roles of an existing user (to get the rights) and how can I see which direct rights the existing user has?
Below query will help you get direct access rights and role rights for a user. and you can assign same to new user
select distinct 'role ' as typeofright, a.grantee as username, a.rolename as rolename, databasename, accessright
from dbc.rolemembers a
join dbc.allrolerights b on a.rolename=b.rolename
select distinct 'direct' as typeofright, username, ' 'as rolename, databasename, accessright from dbc.allrights
Thanks Harpreet, I understand this query will give me any direct rights the user has on a specific database as well the rights held by a user through roles on a particular database.
This is what I was looking for.