2

In SQL Server 2022 (on-premise), I have a login (call it 'UserManager') which I have added to the ##MS_DatabaseConnector##, ##MS_DatabaseManager## and ##MS_LoginManager## fixed server-level roles.

The intention is for this login to be able to create other logins, create database users for those logins, and add those users to database-level roles (db_datareader, etc).

It appears that by being in the above server-level roles, UserManager is able to accomplish the above tasks, apart from being able to add to database-level roles. It does not have permission to do that (or to GRANT SELECT at database level either).

It looks like there's two things that would work:

  1. Create a database-level user for UserManager in every database and add it to the db_owner role for that database. But I wanted to avoid creating a UserManager user in every database.
  2. Add UserManager to the sysadmin server-level role. But I wanted to avoid giving the user that level of permission.

Are there any other solutions that I've missed? It seems odd that ##MS_DatabaseManager## provides the ability to create a database-level user but not to add it to any roles.

Improve this question

2 Answers 2

Reset to default
2

Well it seems like you have done everything according to what Microsoft recommends:

Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role. As an alternative, starting with SQL Server 2022 (16.x), consider using the new fixed server role ##MS_LoginManager##.

Reference: Server-level roles (Microsoft Learn)

If we have a look at the documentation then we find the following information:

##MS_DatabaseConnector##

Members of the ##MS_DatabaseConnector## fixed server role can connect to any database without requiring a User-account in the database to connect to.

To deny the CONNECT permission to a specific database, users can create a matching user account for this login in the database and then DENY the CONNECT permission to the database-user. This DENY permission will overrule the GRANT CONNECT permission coming from this role.

...and:

##MS_LoginManager##

Members of the ##MS_LoginManager## fixed server role can create, delete and modify logins. Contrary to the old fixed server role securityadmin, this role doesn't allow members to GRANT privileges. It is a more limited role that helps to comply with the Principle of least Privilege.

...and:

##MS_DatabaseManager##

Members of the ##MS_DatabaseManager## fixed server role can create and delete databases. A member of the ##MS_DatabaseManager## role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the ##MS_DatabaseManager## role don't necessarily have permission to access databases that they don't own. This server role has the same privileges as the dbcreator role in SQL Server, but we recommend using this new role over the former, since this role exists also in Azure SQL Database and thus helps using the same scripts across different environments.

This is the way to go with SQL Server 2022+ according to the Microsoft article.

The question is: What error message are you receiving when you:

  • create SQL Server logins?
  • create database users?
  • try to assign database users and database rights to database users?
Improve this answer
1
  • "The question is: What error message are you receiving when you...try to assign database users and database rights to database users?" - I believe this is their issue: "##MS_LoginManager##: Contrary to the old fixed server role securityadmin, this role doesn't allow members to GRANT privileges. It is a more limited role that helps to comply with the Principle of least Privilege.". I think this is why they also need to use the securityadmin role. Commented Feb 21, 2024 at 2:58
2

Add UserManager to the sysadmin server-level role. But I wanted to avoid giving the user that level of permission.

You don't need to provision the sysadmin role. Rather you can give UserManager the securityadmin fixed server-level role instead which provides the privilege to grant permissions both at the server level and database level, like you need:

Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

Warning, it can grant most server level roles, though it seems like it can not grant SysAdmin, so you should be good in that regard.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.