CODEWITHSUNDEEP
permissionssybase
user568866
user568866

Reputation:

Duplicating an existing user's Sybase permissions?

How can I copy all the existing Sybase user's permissions to a new account? For example, some tables only have select permission, many procs have execute permission, etc. The original account had limited access to a couple dozen tables and several stored procs, out of hundreds of database objects.

Upvotes: 0

Views: 2869

Answers (1)

user1505078
user1505078

Reputation: 119

If you are going to do this multiple times: Create a role, grant permissions to the role, and assign users to that role.

If auditing who is doing what in the database is not critical: Alias the new user to the old user in the database

sp_dropuser 'NEW_USERNAME'
go
sp_addalias 'NEW_USERNAME', 'EXISTING_USERNAME'
go

sp_helprotect EXISTING_USERNAME will print out a list of permissions for that user. You can capture that output and use it to add those permissions to a new account.

The following steps require editing system tables, be vary careful to avoid breaking your system. Ensure you have good backups. *

User permissions are stored in the sysprotects table. In theory you should be able to:

  1. Create New User Login
  2. Add User to Database
  3. Add any roles or aliases
  4. Select permissions from sysprotects where the uid = EXISTING_USER_ID into a temp table.
  5. Update the temp table and change the uid from the EXISTING_USER to the uid for the NEW_USER
  6. Use sp_configure to allow updates to system tables
  7. Use the updated temp table to insert records into sysprotects
  8. Use sp_configure to disallow updates to system tables

DISCLAIMER Please test this before trying it on a production server, as I have not tested this to verify it works.

Upvotes: 1

Related Questions