Understanding SQL and Microsoft GP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hupton - 28 Aug 2008 15:49 GMT

I am trying to get and understanding of the security with SQL and GP.
Unfortunately, I know alot about GP but virtually nothing of SQL. Our IT
person handles the SQL side. The confusion I am running into with with some
of these new tools with GP10 there are rights you have to have to deploy
these things. I am not allowed access to SQL only what I have through access
to GP. My IT person says that we cannot have any rights to SQL because that
would open us up to the entire database. We are housing more than just GP
databases on the SQL2005 Server. Are we mising something with security to be
able to effectively utilize more tools/functionality with GP? I hope this
makes sense. We have not upgraded to 10 yet but will be mid-September and I
would like to get some clarification about security. I do not have the SA
password. Do other companies allow someone in the finance department (we are
the main users of GP right now) have that SA distinction. Sorry for the
length but I am trying to understand this very confusing issue. Our IT
people have not been trained in the realtionshop of SQL and GP. They only
have the SQL knowledge. I feel we have a communication gap. Any suggestions?

Reply to this Message

Tom Moreau - 28 Aug 2008 15:56 GMT

Typically, finance people do not have the sa password - nor should they. A
DB server is to be maintained by a qualified DBA. Access can be granted on
a granular basis. For example, you could be given DBO (database owner)
rights to the GP DB, but absolutely no other rights within SQL Server.

Your IT people probably don't need to be trained on GP in order to support
the DB side of things. All they would be interested in would be backups,
index maintenance, etc.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

I am trying to get and understanding of the security with SQL and GP.
Unfortunately, I know alot about GP but virtually nothing of SQL. Our IT
person handles the SQL side. The confusion I am running into with with some
of these new tools with GP10 there are rights you have to have to deploy
these things. I am not allowed access to SQL only what I have through
access
to GP. My IT person says that we cannot have any rights to SQL because that
would open us up to the entire database. We are housing more than just GP
databases on the SQL2005 Server. Are we mising something with security to be
able to effectively utilize more tools/functionality with GP? I hope this
makes sense. We have not upgraded to 10 yet but will be mid-September and I
would like to get some clarification about security. I do not have the SA
password. Do other companies allow someone in the finance department (we
are
the main users of GP right now) have that SA distinction. Sorry for the
length but I am trying to understand this very confusing issue. Our IT
people have not been trained in the realtionshop of SQL and GP. They only
have the SQL knowledge. I feel we have a communication gap. Any
suggestions?

Reply to this Message

hupton - 28 Aug 2008 16:16 GMT

Thank you for that. I totally understand the security aspect. I don't want
access to SQL server because I have no database knowledge. However, how do
the tools that gain access to the database (Excel Reports, SQL reporting
services) tie in to that? I am not sure how to get that relationship set up.
As DBO do I have rights to extract that reporting data from the GP database
and how does that happen? Obviously, I cannot. YOu have to be logged in as
SA to even deploy the Excel reports.

IT will get tired of me running to them every time we need to set something
up like that. Whose responsibility is it for the information relating to
these types of things. I am out of my element trying to get this
understanding. But we are trying to use the system to a greater degree and
some of these things need to be worked out internally. I just need some help
on how to approach so I can have some sort of working knowledge. Thanks so
much.

> Typically, finance people do not have the sa password - nor should they. A
> DB server is to be maintained by a qualified DBA. Access can be granted on
[quoted text clipped - 24 lines]
> have the SQL knowledge. I feel we have a communication gap. Any
> suggestions?

Reply to this Message

Tom Moreau - 28 Aug 2008 17:01 GMT

If you need sa privileges to run Excel reports, then something is wrong.
You shouldn't even need DBO rights. Rather, being a member of the
db_datareader role should be sufficient. I would suggest meeting with your
DBA and having him profile a session where the Excel reports are being run.
Then (s)he should be able to set up security properly.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Thank you for that. I totally understand the security aspect. I don't want
access to SQL server because I have no database knowledge. However, how do
the tools that gain access to the database (Excel Reports, SQL reporting
services) tie in to that? I am not sure how to get that relationship set
up.
As DBO do I have rights to extract that reporting data from the GP database
and how does that happen? Obviously, I cannot. YOu have to be logged in
as
SA to even deploy the Excel reports.

IT will get tired of me running to them every time we need to set something
up like that. Whose responsibility is it for the information relating to
these types of things. I am out of my element trying to get this
understanding. But we are trying to use the system to a greater degree and
some of these things need to be worked out internally. I just need some
help
on how to approach so I can have some sort of working knowledge. Thanks so
much.

"Tom Moreau" wrote:

> Typically, finance people do not have the sa password - nor should they.
> A
[quoted text clipped - 30 lines]
> have the SQL knowledge. I feel we have a communication gap. Any
> suggestions?

Reply to this Message