Permissions required for 'CREATE USER' in SQL Server 2005?

Question

Hi - I am trying to create a SQL server login and database user from within my application, along with a custom application user row. I want these users to be able to create other users - i.e. the application will control who can/can't create users but I need all users to have permissions for creating SQL server logins and database users.

I have got the server login permissions working - i.e. an existing user/login can create a new login - by adding the logins to the 'securityadmin' server role - which grants the 'ALTER ANY LOGIN' privilege.

I tried to do the same with the database users - adding them to the 'db_accessadmin' database role - which supposedly grants the ALTER ANY USER privilege, which is required for CREATE USER.

However any time I try to create a new database user using a user with the above privileges I get a permissions exception.

I have tried manually granting the ALTER ANY USER permission to a particular user (GRANT ALTER ANY USER TO demouser) but this doesn't work either.

Any ideas about what I am missing here?

Thanks in advance!

Answer 1

My bad - I have found the issue - it was not the CREATE USER that was failing, but a subsequent call to 'sp_addrolemember'. This requires further permissions that I wasn't assigning.

In particular I needed to add my users to the db_owner database role in order to allow them to assign other/new users to fixed database roles.

Is there a cleaner way to allow me to achieve what I am trying to do here - i.e. create users that are allowed to create other users?

Answer 2

This seems very dangerous, easily becoming a security nightmare. Not knowing anything about why you think this is the best solution to accomplish your objective I can't really tell you not to do it this way, but wow!! - I would think long and hard about whether this really is necessary. The spider-webbing of users just seems like it could quickly be impossible to manage from a DBA perspective.

Would you not be able to just have one SQL account that has the permissions to add users, and the application uses that every time to add new users? Those users then would not need the ability to add other users. Maybe this won't work for your specific objective, but surely there is some other way.

But having said all that ... no, there is not really a cleaner way. The user would have to be assigned to the correct roles in order to have the ability to later add other users.

Answer 3

Technically, yes. Whether it's right or wrong... no comment.

Anyway, database security is split into 2 functions:

• db_accessadmin to manage users (or "ALTER ANY USER" permission as you mentioned)
• db_securityadmin allows you to manage roles memberships and object permissions (or "ALTER ANY ROLE permission)

This is mentioned for sp_addrolemember.

You are actually changing the role, not the user, by running sp_addrolemember so "ALTER ANY ROLE" is enough without having full db_owner rights.