All Forums Database
usmans 39 posts Joined 01/13
22 Jan 2013
How to assign rights of existing user to a newly created user?

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:

  • Direct Rights
  • Access Rights via a Role

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?

--Regards Usmans
23 Jan 2013

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.

usmans 39 posts Joined 01/13
24 Jan 2013

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?


Harpreet Singh 101 posts Joined 10/11
24 Jan 2013

Hi Usman,
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
and a.grantee='tduser'
select distinct 'direct' as typeofright, username, ' 'as rolename, databasename, accessright from dbc.allrights
where username='tduser'

usmans 39 posts Joined 01/13
10 May 2013

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.


You must sign in to leave a comment.