0

One of our developers just mentioned how he created a login on a server. I didn't think he should have permissions to do that, so I went and checked.

I've looked at which logins have sysadmin, and he's not a member of any of those groups. There are no logins in the security admin role. There also don't seem to be any ALTER ANY LOGIN granted in the sys.server_permissions table.

Is there anything else I can look for here?

1
  • From BOL: In SQL Server, requires ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role. + In SQL Database, only the server-level principal login (created by the provisioning process) or members of the loginmanager database role in the master database can create new logins. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server. If you can't see the new login, question is, did he really create it on this instance? May 23, 2017 at 12:40

1 Answer 1

2

As first thing I'd check server level permissions of that login:

exec as login = 'TheLogin';
select * from sys.fn_my_permissions(null, 'server');

As second thing I'd check login's groups/roles, like this:

exec as login = 'TheLogin'; 
select *
from sys.login_token
where principal_id > 0;

And my future actions depend on what I've got for now. The thing may be tricky: One can have impersonate on sa or be a db_owner of trustworthy's database, so he has "hidden" permissions

9
  • The first query has a row with ALTER ANY LOGIN for the server. The second query has 10 rows with various logins & roles in.
    – BeanFrog
    May 23, 2017 at 13:12
  • There's only one trustworth database, and he's not db_owner on that. How can I tell if he can impersonate sa?
    – BeanFrog
    May 23, 2017 at 13:15
  • This means that your case is simple: one of the listed groups/roles returned by the second query has ALTER ANY LOGIN given explicitly or is the member of securityadmin.
    – sepupic
    May 23, 2017 at 13:15
  • To your second question: only the members of db_owner fixed database role can elevate their permissions if the database is trustworthy but it's not your case
    – sepupic
    May 23, 2017 at 13:17
  • What is the second query showing me? Is it all the groups / roles that this login is a member of?
    – BeanFrog
    May 23, 2017 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.