Right now my issue is smartlist builder needing the sa user in GP, but I'm struggling in general with how security really works in GP.
I'm not sure if what I've been told and what I've seen so far are really accurate.
1. The GP client hashes the password that it assigns to every SQL login.
-
- In this way it doesn’t matter what the logon has access to in SQL because the user can’t login because they don’t know the password for their SQL login.
- The sa password is the only GP password not hashed
- There is some SQL administration that needs done on the GP’s instance from Management Studio
- So we give DBAs Windows Logins access to individual databases
- We can’t make them a sysadmin because there is sensitive data in GP that we probably want a DBA looking at .
- The only real way to deny SELECT on that data is to have them not be a sysadmin
- The sa account needs to be used for just about anything that happens outside of the regular GP client.
- The SQL server reporting service wizard
- Publishing views in smartlist builder
- Etc…
So I’m hoping that there is something we’re missing here.
Basically what we’ve done is to give the owner the sa password and we have him type it in if we need it.
In theory that sounds fine but in the few months that we’ve been using GP 2010 there is need of that password lurking around every corner.
Last week we needed to publish reports to SSRS and we tried to use the reporting service wizard and needed the sa password to log in.
Now we have a view that needs to be published in smartlist builder and we seem to need the sa password.
It seems like this would be a non-issue if GP just used field or row level encryption to mask the data in sensitive tables.
Am I missing something here?
Is this really normally this convoluted ?
