02-11-2011 02:51 AM
Hi there, we're using task centre with our SLX implementation, and some time ago they told me to execute GRANT VIEW ANY DEFINITION TO SYSDBA on the database to fix an issue when creating triggers (task centre doesn't shows the tables of the database when logging with the sysdba user)..
But I never do that, as I'm concern about changing anything to the sysdba user, as long time ago, changing a permission to this user on the database, makes the whole application to stop..
So, is it safe to execute this? (of course, I'll make a backup prior to anything...)
02-11-2011 06:10 AM
What you were told is exactly what needs to be done. - We are a TaskCenter BP as well - we know ;-)
Now you do NOT have to use the sysdba user when doing the authentication in the TC Trigger tool. BUT if you use another (SQL) user, you would have to deal w/a host of additional (SQL) rights. Better to do it w/sysdba and get it done cleanly.
02-11-2011 02:55 PM
"I'm concern about changing anything to the sysdba user, as long time ago, changing a permission to this user on the database, makes the whole application to stop..."
That was probably a case of you (or someone) assigning a server role (sysadmin or dbowner) to the SYSDBA user in SQL Server. When you do that, SQL Server assigns the "dbo" (i.e., default owner) schema to the SYSDBA user account, at which point any application that logs in with the SYSDBA account has to prefix all table references with the original schema name of each table owner (in SLX's case, this is also "SYSDBA"). Since SalesLogix isn't written so as to include those prefixes in its SQL queries, you get "object not found" errors pretty much immediately - if objects like tables aren't part of the user's default schema, they're treated as if they don't exist, unless references to them are qualified with a schema prefix.
You might think this is a deficiency on SLX's part, but if all table references in queries were qualified, then SLX could be run under practically any SQL Server user account, and it would actually be much less secure as a result. Anyway, it's a fairly simple explanation when you think about it, but you rarely see it explained among SalesLogix folks...
Anyway, explicitly granting a specific access permission (such as "View Any Definition") isn't the same as assigning a server role. Permissions like that don't affect the schema assignment, so it doesn't (or shouldn't) affect the interpretation of SQL queries (and thereby the operation of the program).
10-13-2012 06:02 PM
So how do you fix it if you do grant sysadmin rights to the sysdba? I did not realizing it would cause such chaos with SLX and now the dbo user-defined data types aren't recognized by the sysdba.
10-14-2012 09:59 AM
OK. I am assuming you are NOT talking about:
"GRANT VIEW ANY DEFINITION TO SYSDBA"
but rather "role" assignment because doing "GRANT VIEW ANY DEFINITION TO SYSDBA" is not an issue.
To fix the "role" problem, you just need to get into the the sql "logins" in SQL Server management studio and just un-check ALL roles you may have assigned to the sysdba user.. and make sure the "default db" is "master.
10-15-2012 08:48 AM
Thanks for this, "To fix the "role" problem, you just need to get into the the sql "logins" in SQL Server management studio and just un-check ALL roles you may have assigned to the sysdba user.. and make sure the "default db" is "master."...however, it did not, I even restarted the SQL Server service.
My issue has nothing to do with the "grant view...", I just found this chain when searching for an answer of why the dbo's user-defined data types were no longer visible after I made the sysdba a 'sysadmin' (then removed since it broke SalesLogix).
Any other ideas? sysdba only has the 'public' role and the default db is 'master'.
Thanks ahead of time.