May 25, 2010 at 8:45 pm
Correct me if I'm wrong or please explain to me if this is functioning as designed.
I have a need to create a middle tier database group; basically power users at the instance level.
I was working through the possible combinations of built in server roles and haven't found any other posts discribing this senario.
1> I (sysadmin) create a user.
2> I grant that user the securityadmin role because I need that user to create and manage accounts.
3> I log in as the securityadmin user. I verify that user cannot alter accounts above their privilege or perform functions outside their role.
sp_configure 'show advanced options','1'
'User does not have permission to perform the action'
Create database mytest;
CREATE DATABASE permission denied in database 'master'
Can't reset the SA password.
All is good.
4> I create a new user using the securityadmin account.
5> As securityadmin I grant the new user Control Server Permissions at the instance level.
6> I log in as the new user.
7> Now I can configure the server & create/drop databases because of the permissions granted via Control Server.
Am I missing something here or is that not an elevation of privilege? I need to be able to grant this role, but the hole it creates is too big. How can i prevent securityadmins from creating accounts that elevate their privileges? DDL triggers?
Thanks!
July 16, 2010 at 12:25 am
nothing?
July 16, 2010 at 12:48 am
I haven't tried your example, but it does sound possible, so I'll take your word that it works.
As you've described it, it does allow a SecurityAdmin to create a new account that has elevated access. The best scenario I can think of for this would be for a DBA in an organisation that mandates that IT staff have two logins - a normal user type (granted SecurityAdmin), and an elevated SysAdmin login, and the user has to use Run As to get Management Studio to run as his elevated account.
The normal user account can then manage users (a routine task) without accidentally modifying server properties (dropping databases, etc).
Alternatively, if you disabled SQL logins, then anyone with a SecurityAdmin account would only be able to create logins that are in Active Directory. This means that he would need multiple accounts to log in as (assuming he can't elevate himself).
Can you elaborate on your desired goal? I'm guessing you want a standard user to manage logins for your application? Can this instead be controlled by membership in Active Directory groups and grant the group access to SQL Server?
July 21, 2010 at 9:39 am
This should be filed as a bug. Checking into it.
K. Brian Kelley
@kbriankelley
July 21, 2010 at 3:14 pm
Thanks for the replies, it seemed like a bug to me.
Jim to elaborate on what I'm doing:
I work for a large government org that has several sub orgs in it. Each org had its own IT groups, Unix/Windows/Networking/DBAs etc.
Recently TS (technology services) was split into its own org as a service provider to the others and most of the IT departments merged, but application ownership remains with the orignial orgs. TS is basically infrastructure and in theory an internal cloud.
When the Database group was scoped there was a lot of pull to keep DBAs with the appilcations and obviously TS needed a DBA group. End result is I have two DBA groups that have completely different reporting structures that will intermix on the servers.
TS DBAs are to be sysadmin and responsible for instance level compliance and security across the entire government agency. Application DBAs are dbo by default. The problem is the app dba's are not necessarily junior (they were the sysadmins before the split) and perform functions that may require more than dbo.
So this is why we're looking at delegating the various rolls. It allows the TS DBA's to off-load some of the calls to the qualified App DBAs without violating the sysadmin policy.
July 21, 2010 at 5:08 pm
OK, it's not a bug. It's expected behavior. There will be an update to Books Online accordingly to clarify things.
Because securityadmin has the ability to grant any server permission, it is expected that it can grant CONTROL SERVER to a login. This isn't clearly explained in Books Online. Therefore, because securityadmin has the ability to create a login (and thereby know the login's password) and give that login CONTROL SERVER rights, securityadmin should be treated as sysadmin.
The fixed roles are included mainly for backward compatibility sake, and granular permissions are the recommended approach. Unfortunately, this doesn't give you everything, meaning you have to stay on top of things if you've got new databases being created because you've got to basically ensure that when a new one comes online, you've got to grant the granular permissions to connect to the database and create users. Here's basically the equivalent permissions to what you're probably seeking to do. Note that the database permissions have to be done on every database they should have the ability to control access to. Also, I'm only stopping at the CREATE USER stage. If you want to have said login be able to grant more, then additional permissions at the database level or below will be required.
CREATE LOGIN SomeLogin WITH PASSWORD = 'SomePassword!1234';
GO
GRANT ALTER ANY LOGIN TO SomeLogin;
GO
USE SomeDatabase;
GO
CREATE USER SomeLogin;
GO
GRANT ALTER ANY USER TO SomeLogin;
GO
Obviously, if you have Windows groups, use the appropriate syntax at the CREATE LOGIN level. Now to write this up sometime tonight as a blog post for tomorrow.
K. Brian Kelley
@kbriankelley
July 21, 2010 at 5:15 pm
One other idea is if you truly want all new databases to have this user with this permission per K. Brian Kelley's example, you can create this user and permission grant in model db. Then all new databases will have this by default.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply