Question Re: getting errors user not allowed to backup encrypted d... in Technology Q&A

getting errors user not allowed to backup encrypted databases other then SA login. pl advise alterna

db1231 — Tue, 11 Feb 2025 03:49:49 GMT

on ASE 1.0 I have performed below for database encryption with SA login. Successfully able to dump and load databases on source/target with SA login. When tried with non-sa login which with oper_role, getting error

use master
go
create encryption key master with passwd "xyzpassword"
go
set encryption passwd 'xyzpassword' for key master
go
create encryption key dbkey for database encryption
go

alter database db1 encrypt with dbkey

with sa login - WORKS able to dump db1 from source and refresh to target.

Error - with user login with oper_role, getting error user xx not allowed to dump encrypted db, has to be a db_owner.

Also, does the transaction logs has to be on hold while the encryption is in progress.

Any brief document, listing the steps is very helpful.

Re: getting errors user not allowed to backup encrypted d...

bo2l — Wed, 12 Feb 2025 08:21:16 GMT

What is your ASE server version? Please get output of the followings:

select @@version
go
sp_helpdb db1
go
sp_displaylogin LOGIN_NAME
go

And please share the complete error text you received.

Re: getting errors user not allowed to backup encrypted d...

ChrisBaker — Wed, 12 Feb 2025 15:03:02 GMT

Are granular permissions enabled?

Re: getting errors user not allowed to backup encrypted d...

db1231 — Wed, 12 Feb 2025 16:38:00 GMT

daptive Server Enterprise/16.0 SP04 PL05/EBF 30841 SMP/P/x86_64/SLES 12.4/ase160sp04pl05x/3611/64-bit/FBO/Fri Dec 29 02:49:10 2023 (1 row affected) 1> use db1 2> go 1> sp_displaylogin xyz 2> go Suid: 28 Loginame: dbtsops Fullname: DBTS Operations Default Database: master Default Language: Auto Login Script: Configured Authorization: oper_role (default ON) Locked: NO Date of Last Password Change: xxxxxxxx Password expiration interval: 0 Password expired: NO Minimum password length: 6 Maximum failed logins: 0 Current failed login attempts: Authenticate with: ASE Login Password Encryption: SHA-256 Last login date:xxxxxxxx Exempt inactive lock: 0 (return status = 0) 1>

Re: getting errors user not allowed to backup encrypted d...

db1231 — Wed, 12 Feb 2025 16:41:26 GMT

Hi Chris, This is the error message I am getting. I have attached Info of the server version and the sp_displaylogin. 125/02/10 20:13:31 Changed database context to 'master'. 125/02/10 20:13:31 Changed language setting to 'us_english'. 125/02/10 20:13:31 User 28 not allowed in database 'db1' - only the owner of this database can access it. ABORT ====> dbsqlexec error

Re: getting errors user not allowed to backup encrypted d...

db1231 — Wed, 12 Feb 2025 16:42:46 GMT

No Granular permissions enabled. I am looki

Re: getting errors user not allowed to backup encrypted d...

db1231 — Wed, 12 Feb 2025 16:44:35 GMT

No Granular permissions enabled. I am looking for any pointers, which would have dbs encrypted with minimal permissions for the non sa loginsto dump and load the databases. Thank you sir! for your assistance with this,

Re: getting errors user not allowed to backup encrypted d...

Mark_A_Parsons — Thu, 13 Feb 2025 14:38:39 GMT

The message - User 28 not allowed in database 'db1' - only the owner of this database can access it. - is generated when a database has been set to dbo use only and a non-dbo user attempts to access said database.

Has database db1 been configured with dbo use only set to true?

Re: getting errors user not allowed to backup encrypted d...

db1231 — Fri, 14 Feb 2025 02:20:30 GMT

encrypted db not in dbo use mode, Any granular level access other then sa_role