May 19, 2016 at 5:34 am
Hi all,
This is an odd one but I've recently started a new role at a new organisation and I'm finding that my permissions (db_datareader, db_datawriter, db_owner etc) are being deleted overnight along with any tables, views, stored procs etc that I have created. I've asked the DBA here to look into this and advise which overnight process or job is causing this and fix it, but until now he's seemed quite agnostic to it. He's happy to reinstate my permissions manually each day but he must get bored of doing this as I know I get bored of requesting it.
I've escalated the matter and I'm waiting to see what comes of it, but my question is: as I currently have permissions (until I log off and go home) are there any logs that I can view or generic scripts that I run to get a list of the overnight activity so that I can try to get to the bottom of it myself?
Any help would be appreciated.
Thanks.
May 19, 2016 at 5:37 am
Sounds like the database is getting restored from some other backup every night. Check the SQL error log for entries noting the DB has been restored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2016 at 5:41 am
Hi,
Thanks; where/how could I access the logs?
May 19, 2016 at 6:09 am
Lord Lucan (5/19/2016)
Hi,Thanks; where/how could I access the logs?
This is the 2008 link, but it should still help. Though given what permissions you listed above and the fact that you're not a DBA, you might not be able to see these logs or get into the server to view them.
The GUI way to view them is to Click SQL Server Agent. Error Logs for both SQL and the Agent are under there. Again, you may not have permissions to view this.
At my workplace, we put a Fix & Update User Permissions step on our restore jobs. The step includes reconnecting the SIDS of existing logins to the restored databases (using Sp_Change_Users_Login) and then T-SQL that creates users that don't exist and adding them back to the appropriate database roles. Talk to your DBA and see if (s)he can add this to whatever is restoring the databases if indeed the database is being restored.
The alternate explanation for this behavior is some sort of security sweep that removes individual permissions. If you're hooked up to a Production DB instead of a Dev / QA DB, then it is entirely possible someone has created a job (probably due to SOX or HIPAA or some other law) to remove extraordinary permissions from Production every night. In which case, you may not have a choice but to go through this every day.
Either way, talk to your DBA to see if (s)he knows what's going on here and if there's a work around so the two of you don't have to burn working hours to get the issue resolved.
May 19, 2016 at 6:34 am
Brandie Tarvin (5/19/2016)
The alternate explanation for this behavior is some sort of security sweep that removes individual permissions.
And
along with any tables, views, stored procs etc that I have created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2016 at 6:35 am
Lord Lucan (5/19/2016)
Hi,Thanks; where/how could I access the logs?
Easiest way, ask your DBA for them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2016 at 6:37 am
GilaMonster (5/19/2016)
Brandie Tarvin (5/19/2016)
The alternate explanation for this behavior is some sort of security sweep that removes individual permissions.And
along with any tables, views, stored procs etc that I have created.
I did read that. I didn't apparently remember I read that when I responded. :blush:
May 19, 2016 at 7:08 am
Hi guys,
thanks for the responses and for wording them in english for me 🙂
I will try speaking to the DBA again but so far it's fallen on deaf ears as he has other jobs to do which he's presumably judging as being more important that little old me. From previous conversations with the DBA he's told me the db I'm being disconnected from is in fact a copy of a prod db so it's possible that the refresh that pushes new data over the old is removing my permissions, What's odd is that this issue is only effecting me and no other users...
Either way from what you've both said it's something beyond my capability. For what it;s worth my permissions are such that I cannot view the SQL Server Agent option in object explorer...
May 20, 2016 at 10:42 am
You could have a role in production created that has your permissions and then a script to add your login/user to the role when the restore happens. Or if you have production rights, this would transfer.
You'll need the DBA to help get this setup. Or have them add a script to the restore job that adds permissions for your account.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply