User management for a specific database

Copper Contributor

As a newcomer to MS SQL Server, I'm having trouble giving the user 'gis' with login the ability to create additional users in a specific database T and assign permissions (which I have already accomplished). At the same time, the user should not be able to see or modify other databases' users (or logins). Here are some additional details:

  • 'sa' is the owner of the T database
  • The 'gis' user has already been created as a user of the T database and is a member of the db_owner role
  • 'gis' is the owner of the 'gisdaten' schema (of the database T)

Any help or guidance would be greatly appreciated!

4 Replies

@NikoT780 

 

Hi, Niko.

 

The sysadmin server role is required for managing server logins, while the db_owner and db_accessadmin database roles can manage database users.

 

If your "gis" account is not a member of the sysadmin server role (ideally, it wouldn't be a member of any server role) but is a member of the db_owner database role then you have already fulfilled your requirements, since this allows "gis" to manage users within that specific database and no other database.

 

For example, if you have five databases (including the gis database) on that one SQL Server, then so long as the gis user has only been granted to the gis database and placed in either db_owner (which you have already done) or db_accessadmin, you're done - there's nothing left to do.

 

The fact that the gis user is the owner of the gisdaten schema isn't particularly relevant to your stated requirements, since in having db_owner access, the gis user can access all schemas within that specific database.

 

Database role reference (which happens to have a nicely-laid out illustration of the database roles and rights):

 

 

Cheers,

Lain

@LainRobertson 

 

As an afterthought, if you're looking to create a new user (the part you already mentioned) as well as an associated login (the part not mentioned) using the "gis" login, then you can't do that without leveraging a server role, which will then provide too much access since it will allow the viewing and managing of all database users across all databases.

 

If the database is a contained database, you won't need a server login to be created. If the database is not contained, you quite possibly will (unless Windows/Azure groups are being leveraged to grant "scalable" access.)

 

Cheers,

Lain

Hi Lain,

thank you for the detailed explanation. I have created a "contained database". I completed the following steps:

Step 1:
USE [master];
GO
EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

Step 2:
USE master;
GO
ALTER DATABASE [Geodaten] SET CONTAINMENT = PARTIAL;
GO

Step 3:
USE Geodaten;
GO
CREATE USER testgis1 WITH PASSWORD = 'password';
GO
ALTER ROLE db_owner ADD MEMBER testgis1;
GO

The user is created in the Geodaten database. However, when I try to connect to the database using MS SQL Studio, I receive an error message (Error: 18456).

Best regards
Niko

@NikoT780 

 

When you're connecting using SSMS, are you specifying the database to connect to on the "Connection Properties" tab?

 

If not, it'll be trying to authenticate to the master database, which isn't going to work.

 

LainRobertson_0-1679359263267.png

 

Cheers,

Lain