How to grant a new user the same direct grant privilege as an existing user in sql

Question

How to grant a new user the same direct grant privilege as an existing user in sql

mayo.fadelu

Newbie

Points: 9
More actions
February 18, 2014 at 2:15 pm

#304603

I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

people get replaced a lot, but their job and permissions are more static. Switch to using roles instead.

there are a ton of scripts on the scripts section to script out a users permissions, including object-specific permissions.poke through the contributions and find one you like.

so the right thing to do is:

1. create a script with those permissions from a script found in the script section..

2. create a role.

3. change the permissions script to assign those permissions to that new role.

4. assign bot the old guy and the new guy to the role.

5. remove the individual permissions off of the original user.

6. never assign permissions to a user, always to a role instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 2

this seems to do a nice job:

select sys.schemas.name 'Schema'

, sys.objects.name Object

, sys.database_principals.name username

, sys.database_permissions.type permissions_type

, sys.database_permissions.permission_name

, sys.database_permissions.state permission_state

, sys.database_permissions.state_desc

, state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS

from sys.database_permissions

join sys.objects on sys.database_permissions.major_id = sys.objects.object_id

join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id

join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id

--WHERE sys.database_principals.name = 'your_user_or_role'

order by sys.database_principals.name

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!