Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
Posts: 22
Registered: ‎02-15-2010

Is it safe to execute "GRANT VIEW ANY DEFINITION TO SYSDBA" on the database?

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...)

Regards

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Is it safe to execute "GRANT VIEW ANY DEFINITION TO SYSDBA" on the database?

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.

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Copper Super Contributor
Posts: 33
Registered: ‎11-26-2009

Re: Is it safe to execute "GRANT VIEW ANY DEFINITION TO SYSDBA" on the database?

"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).

New Member
Posts: 2
Registered: ‎10-11-2012

Re: Is it safe to execute "GRANT VIEW ANY DEFINITION TO SYSDBA" on the database?

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.

 

 

Highlighted
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Is it safe to execute "GRANT VIEW ANY DEFINITION TO SYSDBA" on the database?

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.

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
New Member
Posts: 2
Registered: ‎10-11-2012

Re: Is it safe to execute "GRANT VIEW ANY DEFINITION TO SYSDBA" on the database?

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.

 

jo