0

It appears the ALTER permission granted at the database level allows the principal to which it is granted, to create security principals in the database.

Consider the below example:

/* setup database */
CREATE DATABASE MyNewDB
GO

USE MyNewDB
GO

/* create a login and user and then grant ALTER permissions */
CREATE LOGIN MyLogin WITH PASSWORD = 'My57r0ngp455w0rd'
CREATE USER MyUser FOR LOGIN MyLogin
GRANT ALTER TO MyUser
GO

/* running as our new user which only has ALTER permissions, create a user and a role */
EXECUTE AS LOGIN = 'MyLogin'

CREATE USER UserCreatedByMyLogin FOR LOGIN SomeOtherLogin
CREATE ROLE RoleCreatedByMyLogin
ALTER ROLE RoleCreatedByMyLogin ADD MEMBER UserCreatedByMyLogin

REVERT 

/* verify success */
SELECT  USER_NAME(role_principal_id),
        USER_NAME(member_principal_id)
FROM    sys.database_role_members
WHERE   role_principal_id = USER_ID('RoleCreatedByMyLogin') AND
        member_principal_id = USER_ID('UserCreatedByMyLogin') 

The user MyUser is able to create a user, a role and then add the user to the role, with only ALTER permissions.

The documentation for ALTER states

ALTER

Confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope. For example, ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema.

The documentation for CREATE USER states

Requires ALTER ANY USER permission on the database.

The documentation for CREATE ROLE states

Requires CREATE ROLE permission on the database or membership in the db_securityadmin fixed database role.

so none of the documentation appears to outline the behavior I am seeing above. Is this a bug, or am I missing something?

1 Answer 1

2

ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.

USER is a database-scoped securable (see the table in the doc) so CREATE USER permission was conferred by GRANT ALTER on the containing database securable.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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