January 14, 2010 at 4:03 am
Good morning,
I am evaluating the SQL security of the development team that I work in on the live production server currently and things are currently not working 'as intended' and I am now at a loss as to why things are still executing when they shouldn't be!
I understand that the security permissions are inherited from the Schema level --> DB level --> DB Object level but what I am failing to understand that is if the windows login has been created (for this instance ADMIN\UserX) and it has only been given:
- The 'Public' server role
- The 'db_datareader' & 'db_denydatawriter' at the database level
- Deny EXECUTE on a specific object (in this instance a stored procedure)
How is that user 'ADMIN\X' is still able to successfully execute the stored procedure? I'm just not sure as I've followed what I know on this subject and still said user is allowed to execute the SP.
If anybody can help out it would be much appreciated as I want to be able to control the ability of developers to update the production DB!
Thanks in advance,
Dan
January 19, 2010 at 3:53 am
Hi,
I had a similar situation when I first arrived at my current company.
A few things to check.
1.Is the public server role a member of the sysadmin role?
2.Running a exec sp_helprotect (or exec sp_helpProtect NULL, 'ADMIN\UserX') may help you.
3.Or are the users setup as local admin on the development servers and the Builtin\administrator not disabled.
4.ADMIN\UserX has been added locally to the installation groups on the server, i.e SQLServer2005MSSQLUser$[SERVER NAME]
I hope that helps
Paul
January 19, 2010 at 10:03 am
paulm-510034 (1/19/2010)
Hi,3.Or are the users setup as local admin on the development servers and the Builtin\administrator not disabled.
Paul
Aha! A group had been added to the local admin group on the production server (this was a legacy action) as a consequence all developers had been added to the BUILTIN\Administrators group. This in turn meant that all the developers had sysadmin rights based on the way the SQL server groups had been setup. This has now been resolved.
Many thanks for your help PaulM, I was beginning to lose my sanity trying to find the security loophole!
Dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply