Forum Discussion
User management for a specific database
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
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
NikoWhen 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.
Cheers,
Lain
