July 26, 2019 at 6:37 am
Hi Guys,
i started the XEvent Profiler with Standard SQL and TSQL Default Data. At the start of the application, I was able to detect the following events:
select substring('NY',status/1024&1+1,1) from master..sysdatabases where name=DB_NAME()
SELECT name, physical_name AS current_file_location From sys.master_files WHERE(sys.master_files.name='pcdrdatasql' )
SELECT name, physical_name AS current_file_location From sys.master_files WHERE(sys.master_files.name='master' )
could that be the reason?
July 26, 2019 at 8:42 am
GRANT VIEW ANY DEFINITION TO the Application User on master db was the solution.
now the application starts without db owner role. Is there any objection/impact from your point of view?
July 26, 2019 at 12:55 pm
That works. But, you may see additional errors appear when running the app. The app may have something that tries to modify the database(s), such as shrinking(!) or something else.
That's an odd set of code for an app to be running at startup.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 26, 2019 at 1:40 pm
That works. But, you may see additional errors appear when running the app. The app may have something that tries to modify the database(s), such as shrinking(!) or something else. That's an odd set of code for an app to be running at startup.
Why does it care where the physical location of the files are, the whole point of using a DB is to abstract that out? Is it trying to use those for storage?
July 26, 2019 at 2:01 pm
The way I handle this situation is if I trust the vendor, I give them the permissions they specify, if I don't trust the vendor, I don't use their program.
You are choosing a third option that I probably wouldn't because I don't have the resources available to exhaustively test all the vendors code with all possible combinations of internal state. Like Mr. John indicates, how can you be sure that's the only code that needs more permissions? Heck even the vendor support can't answer the question, most likely because the ony configuration tested was the one with the original permissions that you don't like.
someone else wrote:
"this just gets better, they don't know!, what kind of software company doesn't know what the application does and why it does it."
To me this is an incorrect analysis. The vendor TESTED the app with the specified permissions, and as indicated by a post by OP, they actually did do some access with the master database so they really did need more than the permissions OP wanted to grant. In other words, the vendor is the sort of software company that only supports TESTED configurations, ie., isn't this the sort of thing they are SUPPOSED to do? Really, I think "someone else" might not have ever wrote a program with a medium or large team of software engineers producing many thousands of lines of code. If the vendor intended to support the program WITHOUT the specified permissions, they would be well advised to TEST the program WITHOUT the specified permissions rather than leaving it to the end user like "someone else" seems to think is awesome.
another post:
"If it's only access, it is often done through the guest account which is enabled in master and should be kept enabled in master (as well as tempdb and msdb). If guest is still enabled and that app won't start and if just one login for the application needs access, you could temporarily add that login to master and run extended events session or profiler and see what that account is accessing in master or what it needs in master."
This seems to assume that OP has the same testing capabilities that the software vendor has for their SUPPORTED configuration, and that OP can apply the same diligence and code coverage in testing, even though most likely OP doesn't have the vendor's entire code base for analysis, much less any of the developers with knowledge of the code base. So I would not agree with "another post" either, because at best it can only fail, it cannot conclusively determine to any significant degree that OP has sufficiently tested the program to cover all cases. Maybe there's an infrequent exception situation that again needs elevated permissions, and this infrequent except situation could very well be difficult for OP to trigger. How do we know OP can test to cover all programming including all possible internal states?
Hey, chances are that it'll work fine. That doesn't mean I'm going to start recommending recreational gambling on the job. 2 cents.
July 29, 2019 at 8:37 am
If you have not already done so this is something you should escalate to your management.
If you are based in the EU then this level of access puts you in violation of GDPR. Anything that claims to support SQL Server needs to support the SQL Server security model and be able to run with minimum permissions. Not doing this is a breach of GDPR, and the fact you cannot give a justification for needing the access.
The technical solutions suggested of isolating this app into its own instance do not get you any closed to compliance.
My advice is to write a report to your management describing the problem, your understanding of compliance issues, and the choices for proceeding (find another app, mix it in with the rest or isolate is but either way hope nothing goes bang or the regulator does not say FINE, or give the vendor 3 months to fix it). The final decision on how to proceed must come in writing from management.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 29, 2019 at 3:08 pm
That works. But, you may see additional errors appear when running the app. The app may have something that tries to modify the database(s), such as shrinking(!) or something else.
That's an odd set of code for an app to be running at startup.
That works. But, you may see additional errors appear when running the app. The app may have something that tries to modify the database(s), such as shrinking(!) or something else.
That's an odd set of code for an app to be running at startup.
Your comment about shrinking might make sense if it still has some of the MC Access code it was upgraded from. Many many moons ago when I supported a multi-user Access DB setup - we had such code run on startup to detect if a compact and repair were needed. Still that's pretty scary if someone tried to reenable that after moving it to SQL server.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply