How to assign rights of existing user to a newly created user?

Database
Enthusiast

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?

4 REPLIES
Enthusiast

Re: How to assign rights of existing user to a 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.

Highlighted
Enthusiast

Re: How to assign rights of existing user to a newly created user?

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?

Cheers!

Enthusiast

Re: How to assign rights of existing user to a newly created user?

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'

union

select distinct 'direct' as typeofright, username, ' 'as rolename, databasename, accessright from dbc.allrights

where username='tduser'

Thanks

Harpreet

Enthusiast

Re: How to assign rights of existing user to a newly created user?

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.