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

Question

I am trying to create a SQL Server login user and a database user from my application along with the user application user line. I want these users to be able to create other users, that is, the application will control who can / cannot create users, but I need all the users to have permissions to create SQL Server logins and database users.

I have access rights to the server - i.e. an existing user / login can create a new login - adding logins to the server role "securityadmin", which grants the privilege "ALTER ANY LOGIN".

I tried to do the same with 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, whenever I try to create a new database user using a user with the above privileges, I get a permission exception.

I tried to manually grant the user ALTER ANY USER permission for a specific user (GRANT ALTER ANY USER TO demouser), but this also does not work.

Answer 1

Technically, yes. Right or wrong ... no comments.

In any case, database security is divided into two functions:

• db_accessadmin for user management (or, as you mentioned, "ALTER ANY USER")
• db_securityadmin allows you to manage membership roles and object permissions (or "ALTER ANY ROLE")

This is indicated for sp_addrolemember .

In fact, you are changing the role, not the user, by running sp_addrolemember, so that "ALTER ANY ROLE" is sufficient without full db_owner rights.

Answer 2

My bad - I found the problem - it was not CREATE USER that failed, but the subsequent call to "sp_addrolemember". This requires additional permissions, which I did not assign.

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

Is there a cleaner way to let me achieve what I'm trying to do here, i.e. create users who are allowed to create other users?

Answer 3

It seems a very dangerous, easily becoming a security nightmare. Not knowing anything about why you think this is the best solution to achieve your goal, I cannot say that you are not doing it this way, but wow! “I would think for a long time whether this is really necessary.” Web spider users just seem that from the point of view of DBA it can be quickly impossible.

Could you have only one SQL account that has permissions to add users, and the application uses this every time to add new users? These users then will not need to add other users. It may not work for your specific purpose, but there is certainly another way.

But, having said all this ... no, in fact there is no cleaner way. The user must be assigned to the correct roles in order to be able to add other users later.

Answer 4

/* TOPIC: create a login ,who can add other logins to databases (securityadmin server role) */ USE MASTER GO Create login securityTestLogin with password = '@@somepassword123' -----add this to server , this is server level security role ------- EXEC master..sp_addsrvrolemember @loginame = N'securityTestLogin', @rolename = N'securityadmin' --- first this login should be a user in database where we want to give other users access USE HTDBA GO Create user securityTestLogin for login securityTestLogin EXEC sp_addrolemember N'db_accessadmin', N'securityTestLogin' -- depends on your requriemtnt you might also want this permission too --EXEC sp_addrolemember N'db_securityadmin', N'securityTestLogin' GO ------ Now we think about adding other users to different database roles ------------- /* There is one gottcha , db_securityadmin role cannot add users to the fixed database roles ,only db_owner can perform this action , but for security we don't want to give this permission . so we need a work around Create a role with required permission and then add users to that role. */ --Create user defined database role Readers EXEC sp_addrole DBUser -- Add this role to fixeddbroles to get database level permission EXEC sp_addrolemember db_datareader, DBUser EXEC sp_addrolemember db_datawriter, DBUser GO --------READY TO TEST -------- ------ we are using this sample login for test use master Go Create login testlogin1 with password='@@somepassword123' use HTDBA go Create user testlogin1 for login testlogin1 --- now add this user to user created DBUser role . EXEC sp_addrolemember DBUser, testlogin1 

A very good article on SQL permissions:

http://www.sqlservercentral.com/articles/Security/sqlserversecurityfixeddatabaseroles/1231/