November 2, 2023 at 8:21 pm
Hi guys
I have 2014 sql server that has 4 logins. These logins can connect to user databases that they have read/write permissions. These logins are not the owner of 'master' database.
After creating new login with the same permissions, i can only see/connect to 'System Databases' and 'database Snapshots'.
I tried to create same login on different server everything works as expected.
I am assuming that something changed on the server level.
Any idea why login has to be master database owner in order to be able to connect to user database?
Thank you!
November 2, 2023 at 8:46 pm
You'll need to show some code or better explain what's happened. My guess is you haven't created the login with the same permissions.
November 2, 2023 at 8:47 pm
You'll need to show some code or better explain what's happened. My guess is you haven't created the login with the same permissions.
November 2, 2023 at 8:53 pm
Have you verified that there are users tied to those logins in the desired user databases? That they have the permissions you expect?
It sounds like the login was not mapped to user databases, and/or was not given desired roles/permissions.
November 2, 2023 at 9:05 pm
I scripted one of the 'good' login.
CREATE LOGIN [Login1] WITH PASSWORD=N'****',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
Changed login name and pwd
CREATE LOGIN [NewLogin] WITH PASSWORD=N'********',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
Mapped NewLogin with database user
November 2, 2023 at 9:08 pm
Confirmed that NewLogin has read/write permissions by going to NewLogin->Properties->User Mapping
November 2, 2023 at 9:10 pm
And as i mentioned ,
I was able to create NewLogin on different server
November 2, 2023 at 10:02 pm
It seems like you may be confusing server login with database user, or we're not fully communicating on what steps you took to grant user access to other databases.
The scripts you showed did not create users in any other databases
USE [UserDatabase];
CREATE USER [Login1] FOR LOGIN [Login1] WITH DEFAULT_SCHEMA=[dbo];
nor grant connect to any other databases
USE [UserDatabase];
GRANT CONNECT TO [Login1];
Why do you expect the new login to be able to connect to any database other than master?
November 2, 2023 at 10:25 pm
I executed exec sp_change_users_login 'Auto_Fix','NewLogin' on user database.
And then i checked on NewLogin properties on Security section of Login as well as at database security/users/membership .
If i grant NewLogin 'master' database owner permission, everything will be ok.
1.Does Login have to be owner of master database in order to be able to connect to user database(having all necessary database permissions)
2.Is there any SQL Server configuration settings that are SQL Server Login related?
November 2, 2023 at 10:37 pm
Let me rephrase my problem and make it very simple.
I have Login/user (myLogin) which can connect to myDB. myLogin can read/write... on myDB.Also myLogin is owner of master database. If i take master database ownership away from myLogin then i cannot connect to myDB database.
November 3, 2023 at 3:33 pm
What does myLogin look like as a login?
You're describing things, but many of us have seen for years, that descriptions can be incomplete, and you might do something, or have done something, that you are forgetting. It's why we want to see code and scripts, and a repro if possible.
When you say ownership, I assume you mean that the owner of the database is myLogin and not that MyLogin has db_owner role?
What does this return?
USE master;
GO
SELECT
d.name AS [Database Name]
, SUSER_SNAME (owner_sid) AS [Database Owner Name]
, sp.name AS [Owner Name]
FROM
sys.databases d
INNER JOIN sys.server_principals AS sp
ON owner_sid = sp.sid
WHERE d.NAME = 'master';
SELECT
sp.name AS [Principal Name]
, sp2.name AS [Role Name]
FROM
sys.server_role_members AS srm
INNER JOIN sys.server_principals AS sp
ON srm.member_principal_id = sp.principal_id
INNER JOIN sys.server_principals AS sp2
ON srm.role_principal_id = sp2.principal_id
WHERE sp.name = 'myLogin';
If you cannot connect to a user database (myDB) when myLogin is not the owner of master, then likely someone has taken away from rights from public in master. myLogin would need to connect to master as a login in the database and then would map to a user in myDB.
The same thing for listing databases. The public role should be able to view sys.databases so that a list of databases can be returned. My guess is that either you didn't create this login/user with the same permissions, or someone has modified permissions either on the other logins or on the system databases, likely master.
November 3, 2023 at 4:43 pm
Hi Steve.
Thank you for your time and effort.
I run your query and it returned
Database Name Database Owner Name Owner Name
master sa sa
November 3, 2023 at 4:44 pm
Second Select did not return anything
November 3, 2023 at 4:49 pm
FYI
I run same query on different server where mylogin can connect to MyDB and got same results:
First SELECT sa
Second SELECT no results
November 3, 2023 at 5:40 pm
Here are the steps and screenshots:
Server #1
USE master
GO
CREATE LOGIN [MyLogin]
WITH PASSWORD = '######',
DEFAULT_DATABASE = MyDB,
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF ;
USE [MyDB]
GO
CREATE USER [MyLogin] FOR LOGIN [MyLogin]
GO
USE [MyDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [MyLogin]
GO
USE [MyDB]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [MyLogin]
GO
I run same scripts on Server #2
Please see attached screenshot.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply