August 6, 2015 at 12:59 pm
Greetings,
In testing with SQL 2014, I have found that any user that is granted a server role (default roles) has the ability to add any other defined user within the SQL environment to that same role.
Example would be:
I assign the diskadmin role to a user. That user now has the ability to assign that role to another user that didn't have it before.
The user does not have access to securityadmin, however they can simply script it using alter server role to add the member, and it works.
Is there any way to prevent a user from doing this?
I've already tried setting the 'Alter any server role' permissions to deny, and it didn't work.
I'm not sure what i'm missing.
thanks!
August 6, 2015 at 1:29 pm
What was the means used to grant these users access to that role in the first place (the users that are now able to grant that role access to a new user)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 6, 2015 at 1:32 pm
I simply used the GUI.
August 6, 2015 at 1:55 pm
egori (8/6/2015)
I simply used the GUI.
Interesting. I just tested it and am seeing the same thing. I just checked the documentation and it is confirmed that server roles are supposed to behave this way.
The work around would be to create a user-defined server role, then grant the permissions necessary to perform whatever action it is you want the person to be able to perform. User defined server roles do not allow their users to grant membership to anybody else (by default).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 6, 2015 at 2:12 pm
Thank you SQLRNNR.
I just tried creating a new role, assigned the server role that I was originally testing with to it under memberships, and now it is not allowing the user to add others.
It's too bad you can't prevent this with the default roles.
Thanks for your help!
August 6, 2015 at 2:16 pm
YW.
I'd have to agree, membership <> grantor permission.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply