June 18, 2020 at 7:54 pm
Sure, found it, thank you. I know, my habit to laziness caused me to ask a simple question I should not have been asking... yet there is a variation of this question that i honestly searched for and failed to find anything. May be not using a right way of verbalizing my question in google.. but anyway, what happens not to underlying objects but to such permissions as View Database State or Server-wide access level if such is required for statements run inside the Stored Proc? Should user not having such permissions still be able to run an SP that uses objects that require higher access type? this is exactly is what is happening here with me running the 3 SPs here
in the attached, and your SP did not get executed either.
Q2: does my opinion make sense that a user who has permissions for sp_Who2 or Who should also have rights to run both sp_WhoisActive and your procedure? same objects accessed...
Likes to play Chess
June 18, 2020 at 8:52 pm
And I most likely will not be given a View Server State of View Database State. for any database on the server. Yet still need to run procs to collect all kinds of performance (and related) data from all databases on that server.
Likes to play Chess
June 18, 2020 at 10:48 pm
as you are not a DBA what you can do is talk to your DBA's, discuss your concerns with the performance of those databases and see what type of monitoring they have. They may have tools that you are not aware of and if they do they may be willing to give you read only access to those tools assuming they are set to monitor the servers/databases you are interested on.
If they don't have any tool setup already then you can suggest that you use the tools/ scripts you been given here - but you need to ensure you don't annoy them with undue requests, and that you aren't seen as stepping over them to do their job.
If you agree with them and as long as you have your scripts setup correctly and in a way that they extract your desired information without performance impact on THEIR servers they may be able to setup your scripts to run on the servers as part of a Sql Job for example, under THEIR control and give you access to the extracted information.
Regarding some of the permissions - View Server Permission for a non DBA should not be granted unless done through a very specific way that limits the access to your own processes (which is not what you are interested on) - doing otherwise could give you access to information that you are not allowed to have and therefore any good DBA will refuse to give you that permission in most cases (possible exceptions may exist for non shared servers and for individuals that have functions like production support/application admin).
View Database permissions are subject to less restrictions but still have a level of access that again only on some cases should be granted.
June 19, 2020 at 1:30 am
Another solution I found
. This Module Signing looks a bit too involved, but allows very granular permissions for Stored Procedures without giving user higher privileges.
Module Signing is how you accomplish this. https://modulesigning.info
The general concept is:
-Create the stored procedure(s) and/or multi-statement TVF(s) to perform the desired action(s).
-Grant EXECUTE on these modules to whatever user and/or roles need to perform these actions
-Create a certificate
-Sign the module(s) using that certificate (using ADD SIGNATURE)
-Copy the certificate to the [master] database (i.e. create a certificate in [master] using the public key of the certificate used to sign the module(s).
-Create a login from the certificate copied to [master]
-Grant whatever instance-level permissions are necessary to that certificate-based login (which can include adding it to instance-level roles).
For some examples, see:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level:
Likes to play Chess
June 19, 2020 at 1:32 am
// (possible exceptions may exist for non shared servers and for individuals that have functions like production support/application admin).
Yes: ), that's me !
Likes to play Chess
June 19, 2020 at 1:45 am
Sure, found it, thank you. I know, my habit to laziness caused me to ask a simple question I should not have been asking... yet there is a variation of this question that i honestly searched for and failed to find anything. May be not using a right way of verbalizing my question in google.. but anyway, what happens not to underlying objects but to such permissions as View Database State or Server-wide access level if such is required for statements run inside the Stored Proc? Should user not having such permissions still be able to run an SP that uses objects that require higher access type? this is exactly is what is happening here with me running the 3 SPs here
in the attached, and your SP did not get executed either.
Q2: does my opinion make sense that a user who has permissions for sp_Who2 or Who should also have rights to run both sp_WhoisActive and your procedure? same objects accessed...
The difficult part here is that if you've been assigned project "Everything", they're either going to have to provide you with more privs (and, like Frederico states, most good DBAs simply won't do) or you're going to have to write some scripts to build the stored procedures with an "EXECUTE AS OWNER" in them and submit them to the DBAs for code review and deployment. They'll also need to give you privs to execute those stored procedures.
If even that doesn't sit right with them, then you'll need to write the stored procedures to save the results in a table (which you'll also need to provide a script for) and then have them create a job to run it on a regular basis for you. If they're feeling generous, then might give you privs to execute the job on an ad hoc basis.
Things like sp_ShowWorst will make DBAs very nervous because it has dynamic SQL that takes non-parameterized data. The only reason for that is for the sort order, which cannot be parameterized, but is necessary to support the TOP according to what you're looking for. In order to appease the DBAs on such a thing, you would have to write in a bit of code that would guaranteed that SQL Injection would never make it to the execution of the dynamic SQL.
Of course, if you had a job that simply executed a stored procedure that ran sp_ShowWorst with a set of fixed parameters and it wrote the output to a table, I don't believe you'd have a problem with DBA objections but you'd have to make it clear that's what you had done and have them test it to satisfy themselves. They CAN impersonate your login for such testing.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2020 at 1:35 pm
<<< https://www.sql.ru/forum/96127/sp-who3%5B/quote%5DMsg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
DBCC execution completed. If DBCC printed error messages, >>. Jeff, what parameters did you call the sp_Who3 with to get this error?
Likes to play Chess
June 19, 2020 at 2:07 pm
<<< https://www.sql.ru/forum/96127/sp-who3%5B/quote%5DMsg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
DBCC execution completed. If DBCC printed error messages, >>. Jeff, what parameters did you call the sp_Who3 with to get this error?
Run sp_Who3? I never said I ran sp_Who3 so, technically, no errors for me. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2020 at 1:23 pm
Jeff,
Re: sp_ShowWorst
Question: will collecting rows from SYSTEM DBs (i see a lot from msdb, master...) be beneficial to me in any way?
i am thinking to easily modify/limit it to user dbs only, but first checking with you.
THANKS!
Likes to play Chess
June 22, 2020 at 2:03 pm
Jeff,
Re: sp_ShowWorst
Question: will collecting rows from SYSTEM DBs (i see a lot from msdb, master...) be beneficial to me in any way?
i am thinking to easily modify/limit it to user dbs only, but first checking with you.
THANKS!
Yes. If you have rows mostly from msdb and master, that could be either very good (because those typically aren't a problem and your stuff is coming in less than that) or it could be very bad if you're seeing a whole lot of unusual activity on those databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply