July 9, 2021 at 4:08 pm
I have for awhile done the auditor reviews on all SQL Servers. Now, we are in a position where the auditors have agreed to run queries and get the information they need for themselves.
Great. Finally.
But when I give them readonly on master they can't run the required query. Not unless I gave them sysadmin role.
To avoid that, I created a stored procedure called "Auditor_syslogins". I used "execute as" a login that I created. I gave the login db_execute, data_reader. They still get an error message.
Error Message: The execute permissions is being denied on the object "Auditor_syslogins", database master, schema dbo.
I granted "execute" for the login the stored procedure "Auditor_syslogins".
Same error message.
Any one have an idea on how I can give the auditors the ability to run a query to return server roles without making them a sysadmin?
--Server Roles
SELECT member.name AS MemberName ,sys.server_role_members.role_principal_id, role.name AS RoleName,
sys.server_role_members.member_principal_id
FROM sys.server_role_members
JOIN sys.server_principals AS role
ON sys.server_role_members.role_principal_id = role.principal_id
JOIN sys.server_principals AS member
ON sys.server_role_members.member_principal_id = member.principal_id;
Thanks.
Things will work out. Get back up, change some parameters and recode.
July 9, 2021 at 9:48 pm
They should just need EXECUTE permissions as far as I am aware.
I would double check that you granted permissions on the correct object in the correct database on the correct instance (seems silly, but I've made that mistake before) and if you are 100% certain you set the permissions correctly, what about putting it on a user database? Those system views (from my testing) give the same results no matter what database I connect to.
After granting the permissions, I would try impersonating their account and see if you have the same issue they do.
Also, I've found that sometimes when permissions change, you need to close out of SSMS and go back in for the permission changes to take effect.
Now, all of the above being said, I personally don't like auditors touching my system if I can avoid it. I would much rather be in control of what they run on our databases. I had one auditor ask me to run a TSQL query that would have resulted in the hashed versions of user passwords being returned. Now, I know cracking that is going to take a LONG time, but even still, that is data I am not comfortable handing out to anybody inside the company, let alone anyone outside the company!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 10, 2021 at 9:25 pm
My take on it all this is not only "No" but "Hell No".
First of all, it would be a security violation to have a bunch of "unknowns" running a bunch of "unknown" code on your system. Not only do you have the right to know what code is being executed against your system, YOU HAVE THE RESPONSIBILITY TO KNOW!
With that, the simple solution I use is if the auditors want to make sure that neither their code nor their results are tampered with, they are to submit the code to me (the DBA) for review. IF it passes my review for safety, security, etc, then I'll let them know and they can come to my desk or have a remote meeting where they can witness me running their code and sending them the results via secure, encrypted email.
But they're not running it on their own if it needs elevated privs... period.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2021 at 8:42 pm
When we had a credit card payment auditor wanting SQL information, he sat with me, while I ran the queries, and he could see the results.
If one of you is remote, do it with shared screen using Zoom, Teams etc...
July 13, 2021 at 12:04 am
The DBA would not be in direct control of any genuine audit process. It's too much of a potential conflict of interest. Think about it: the company is/may be doing the audit partly to make sure the DBA hasn't granted excessive access to anyone else (such as, say, friends of theirs). If you let the DBA personally and arbitrarily restrict queries that would find such things out, how good is your audit reallly?!
When I was at International Paper, they did fairly robust audits (mega-billion dollar companies tend to do that). I was not allowed to directly restrict what the auditors could see, although I pushed back hard and won when one set of audtors tried to change some setting on the system. Why I'm not sure, but that's a conflict too. If they can change something, then they can potentially allege that I allowed something that I actually didn't.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 13, 2021 at 1:24 am
I do appreciate all of the feedback. However, you are fussing at the DBA who is the low man on the totem pole. AVPs and managers have already made the decision that we allow the auditors to run their queries or the DBA team will do it. My manager wants me to give the auditors the ability to run queries. He wants me to do other projects.
Now having said that, let's get back to the original question please. 🙂
What permissions are needed to query the master database for sys.server_principals. The only thing I have done that works is to create a login and then give that login the sysadmin role.
I would rather not do that. I've granted execute on the stored procedure, the schema and database (db_executor role). I've logged in with that login and I still am not able to run the query successfully.
Thanks.
Things will work out. Get back up, change some parameters and recode.
July 13, 2021 at 2:26 am
Sorry, I thought I had posted some possible solutions. I coulda' sworn I posted that and my response to Jeff earlier but it's obviously not here now. Wish I'd kept a copy of my solutions post, it was rather detailed.
Try:
GRANT VIEW ANY DEFINITION TO [auditor_login];
/* yes, this gives auditors LOTS of read authority. I'm OK with that, as long as they don't change (or try to change) anything. */
I'm virtually certain that will grant them access to sys.server_role_members.
I hope it will also give them access to sys.server_principals. If not, let me know and I will try something else.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 13, 2021 at 2:32 am
I do appreciate all of the feedback. However, you are fussing at the DBA who is the low man on the totem pole. AVPs and managers have already made the decision that we allow the auditors to run their queries or the DBA team will do it. My manager wants me to give the auditors the ability to run queries. He wants me to do other projects.
Now having said that, let's get back to the original question please. 🙂
What permissions are needed to query the master database for sys.server_principals. The only thing I have done that works is to create a login and then give that login the sysadmin role.
I would rather not do that. I've granted execute on the stored procedure, the schema and database (db_executor role). I've logged in with that login and I still am not able to run the query successfully.
Thanks.
Look where you should have started on this journey... see the following link and search for "permissions".
As for the rest of the stuff, DO remember that you're the low man on the totem pole as you've stated and if something goes wrong because of the auditors, it's all on you.
With that being said, do you have an email or anything saying that your boss wants you to do this? If not, you're definitely operating without a net on this "problem". If there is one or more, send it or them to your private, off company site email.
I've not yet been the victim of any underhandedness like that but I have seen it happen to others. Be safe.
Personally, my answer to my boss would still be "NO (with an explanation as to why) but I'll be happy to execute any query under direct supervision after I've reviewed it for safety" and that has worked for me a number of times.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2021 at 2:39 am
Personally, my answer to my boss would still be "NO (with an explanation as to why) but I'll be happy to execute any query under direct supervision after I've reviewed it for safety" and that has worked for me a number of times.
Many companies would fire you for that, not necessarily unjustly. It's not "your" db. It's a company resource. It should be treated as such. If the company wants to give selected outsiders access to that db -- with of course, yes, a cya in writing for you -- then that's a company decision, and almost certainly made above the level of a DBA.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 16, 2021 at 11:56 am
ScottPletcher wrote:It's not "your" db. It's a company resource.
I understand what you are saying. Legally that is correct. But when I am the one that receives the fallout from an audit, my default response is no access to the databases that I "own".
That can change if I get a formal request through the ticketing system identifying who will take responsibility for the change.
July 16, 2021 at 3:11 pm
Exactly. And that's what I meant too.
Jeff said that he'd "still" "say NO". But, once you've made your objections known, and in writing for the record, and management still decides to proceed as planned, at that point you can't just say "no". It's been decided. You can do it "under protest", again in writing if you prefer, but ultimately it is a business decision not your personal decision.
And, as I stated earlier, I don't believe the DBA should be in charge of an audit on that DBA's dbs. Too much of a potential conflict of interest. Genuine auditors should be able to read and review whatever they need to, with normal company and legal guidelines, of course. They should not be able to change anything on the system, however. Auditors did during an audit at one place I was at -- the AD people had given them too much authority -- but luckily the company agreed with me on that one and their capability to update anything was removed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply