Cross database permissions problems

[Former Member]

We have an odd situation with cross database permissions.

Our production database server has multiple databases with many cross database operations (insert, delete, select)

which works without any problems with permissions.

We dump/load the production database into a replica server each weekend which we use for testing purposes.

Occasionally (and I mean very occasionally) on the non-production server we hit issues with cross-database permissions.

     eg DELETE permissions denied on object XXXX, database YYYY, owner dbo line: 179....

All the tables in our databases are either owned by "sa" or dbo (loginame on sysobjects are either allnull or sa).

The sa ones were created by the DBA as the databases were migrated from another server and the null ones

are ones we have created since this migration.

I've read this online but can't tie this back to a ASE document

     "Stored procedures using cross database tables also need select/update/insert

     permission on underlying tables if the tables are not created by

     sa/sa_role/dbo"

If this statement is correct, then as I understand it, we don't need to add permission to the underlying object since

all our objects are owned by sa or dbo. And considering how much cross-database joins we do - then this statement

does appear correct.

Are we understanding this correctly ?

Has anyone else had a similar issue ?

My suspicion is that its related to the dump/load causing an issue with checking loginames with dbo accounts across databases ?

Is it better to have loginames on all objects as null or better to have them as sa (or other) for cross database permissions to work ?

Thanks

Mike

[Former Member]

Thanks

We'll try setting the loginame on the objects

> Is the same login (and same suid) defined as the 'dbo' in the source and target dataservers?

Yes - we've checked this and all looks ok.

> How do you address suid mismatches between syslogins vs sysusers/sysalternates, and role id mismatches between syssrvroles vs sysusers/sysroles?  suid/role-id mismatches can wreak havoc with accessibility issues

(including permissions

Its a good question. The master..syssrvroles are similar between production and non-production but the srid numbers differ. However, the <db>..sysroles tables looks to have "corrected" itself in the dump/load from

production to non-production - in that the sysroles.id column correctly aligns to master..syssrvroles.

Not sure how this happens. Is it automatic as part of dump/load ?

Also, sp_helprotect, sp_displaylogin shows its all correct but I suspect you're right and we have a mismatch somewhere. Is there a good way of resetting all the roles ?Or should we drop all the roles and re-create them so all servers share the same id's ?

[Mark_A_Parsons]

A quick check to see if there's a role mismatch between the local/server roles:

==================

select  u.name as local_rolename, role_name(r.id) as server_rolename

from    sysusers u,

        sysroles r

where   u.uid            = r.lrid

and     role_name(r.id) != u.name

order by 1

==================

As for fixing any role mismatch issues ... *shrug* ... depends on your preferences ... drop/recreate roles (plus permissions) vs patch system tables.  I usually opt for the latter as it allows me to keep the current set of permissions, eg, a) can't always depend on having a master list of permissions in version control and b) can't always reverse engineer sysprotects into grant/revoke commands with 100% certainty.

==================

begin tran

go

declare @rowcount int, @error int

-- update sysroles.id when local/server roles match (by name)

-- but local role id does not match the server role id

update sysroles

set    id = role_id(u.name)

from   sysusers u,

       sysroles r

where  u.uid            = r.lrid

and    role_id(u.name) != r.id

and    role_id(u.name) is not NULL

select @rowcount = @@rowcount, @error = @@error

-- test @rowcount/@error; if issues then 'rollback tran' else 'commit tran'

... >>error_handling<<..

-- delete local role when no matching server role

-- delete sysusers entry while we still have sysusers.uid<-->sysroles.lrid relationship

delete sysusers

from   sysusers u,

       sysroles r

where  u.uid            = r.lrid

and    u.uid           != 1

and    role_name(r.id) is NULL

select @rowcount = @@rowcount, @error = @@error

-- test @rowcount/@error; if issues then 'rollback tran' else 'commit tran'

... >>error_handling<<..

-- now delete orphan sysroles entries

delete sysroles

where  role_name(id) is NULL

select @rowcount = @@rowcount, @error = @@error

-- test @rowcount/@error; if issues then 'rollback tran' else 'commit tran'

... >>error_handling<<..

go

==================