October 22, 2010 at 10:40 am
This is an odd question, but I'm going to ask it anyway...
In SQL 2008, are there any DBA maintenance tasks or jobs that require Sysadmin access and cannot be duplicated by the use of any other server / database fixed role?
What is it that you do now that you couldn't do if your sysadmin access was removed tomorrow? Operate under the assumption you've been given access to all the other fixed server roles on the instance.
October 22, 2010 at 10:53 am
SOX people in?
apply patches.
must be tons more but would have to think about.
Are they trying to take sa away from DBAs?
---------------------------------------------------------------------
October 25, 2010 at 5:16 am
George,
Apply patches is a good one. I forgot about that. If you think of anything more, please let me know.
I've got SQL Configuration and Surface Area Configuration down, but I'm not sure if that's covered by any other server role.
October 25, 2010 at 5:48 am
Brandie,
difficult one. I am always connected with sysadmin so difficult to know for certain what would stop working if my rights were reduced without testing lots of combinations. Pretty much assume all other server roles would be required.
worth bumping the thread in 'the thread'?
---------------------------------------------------------------------
October 25, 2010 at 11:32 am
I'm tempted, but it's too easy to fall into the "My thread is most important so I must link it to "The Thread" " mindset. I don't want to encourage other posters to think that's the quick and dirty way of getting answers to their questions when they don't receive a prompt response. That's not what The Thread is for.
October 25, 2010 at 12:22 pm
securityadmin would be enough to get me anything else I needed.
From SQL Server 2008 BOL:
"securityadmin:
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins."
October 26, 2010 at 5:38 am
Thanks, Michael. I know what the fixed server roles do. What I'm trying to figure out is what they might not cover that would be necessary for a DBA who does pretty much everything.
October 26, 2010 at 8:15 am
Brandie Tarvin (10/26/2010)
Thanks, Michael. I know what the fixed server roles do. What I'm trying to figure out is what they might not cover that would be necessary for a DBA who does pretty much everything.
My point (guess I didn't make it very well ) was really that there is no point to locking out DBAs from sysadmin, since the other roles would allow you to gain sysadmin access anyway.
October 26, 2010 at 8:19 am
Edit: Changing my answer.
It's a semi-good point. A user with every other permission cannot grant herself sysadmin access even when she has SecurityAdmin perms. Which is good from a security standpoint.
However, it still leaves me with my original question.
October 26, 2010 at 9:24 am
Brandie Tarvin (10/26/2010)
Edit: Changing my answer.It's a semi-good point. A user with every other permission cannot grant herself sysadmin access even when she has SecurityAdmin perms. Which is good from a security standpoint.
However, it still leaves me with my original question.
It's a little more work, but someone in the securityadmin role can give themselves sysadmin access.
If you have a login named 'test' in the server securityadmin role, the following will do it.
create login test2 with password = 'test2', check_policy = off
go
grant impersonate on login::sa to test2;
Then login with test2 and execute this:
execute as login='sa'
go
exec sp_addsrvrolemember @loginame= 'test' , @rolename = 'sysadmin'
There are probably a number of other ways to do that, but that illustrates my point.
October 26, 2010 at 9:56 am
I'll double-check the impersonate, but when I logged in as the SecurityAdmin account, I was unable to grant actual SysAdmin to my own account.
October 26, 2010 at 9:59 am
Michael,
EDIT again: I take back everything I just said. I forgot to re-login. Crap. What was MS thinking when they did this?
I just tested impersonate. You're working off an invalid premise. You're working off the idea that the sysadmin account grants the impersonate permission. An account with SecurityAdmin trying to grant SA impersonate to herself gets:
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
Nice try, but no go. A SecurityAdmin cannot get herself or himself "higher" permissions.
EDIT: Following through with your code, while I can create the second login (under my securityAdmin account) with a "commands completed successfully" message, when I run the second bit of code, I receive:
Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
And SA does indeed exist on our server.
October 26, 2010 at 10:14 am
Make sure you follow that code the way I posted it after editing, because I initially posted it wrong. I have tested this several times:
Just to go through it again, first run the following (using a sysadmin account) to create account 'test' in securityadmin role:
create login test with password = 'test', check_policy = off
go
exec sp_addsrvrolemember @loginame= 'test' , @rolename = 'securityadmin'
Then login to the 'test' account and run the following:
create login test2 with password = 'test2', check_policy = off
go
grant impersonate on login::sa to test2;
Then login to the 'test2' account and run the following:
execute as login='sa'
go
exec sp_addsrvrolemember @loginame= 'test' , @rolename = 'sysadmin'
go
exec sp_helpsrvrolemember @srvrolename = 'sysadmin'
As I said before, I am sure there are a number of other ways this could be done.
October 26, 2010 at 10:17 am
Michael Valentine Jones (10/26/2010)
Make sure you follow that code the way I posted it
Yeah, my edit must have crossed your posting.
October 31, 2010 at 8:51 am
Brandie, in case you haven't seen it Brian Kelleys article from this weeks database weekly touches on what you are looking at:
Looking at this it would seem if you scanned system stored procedures and stored procedures in msdb for '(IS_SRVROLEMEMBER(N'sysadmin')' you could get a list of what would be affected.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply