CODEWITHSUNDEEP
sql-serversql-server-2005authenticationpermissions
Ben Jones
Ben Jones

Reputation:

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

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.

Upvotes: 5

Views: 45040

Answers (4)

Hemanshu
Hemanshu

Reputation: 11

/* 
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/

Upvotes: 0

gbn
gbn

Reputation: 432301

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.

Upvotes: 9

Carlton Jenke
Carlton Jenke

Reputation: 3025

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.

Upvotes: 0

Ben Jones
Ben Jones

Reputation:

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?

Upvotes: 1

Related Questions