MSSQL: User - login map > Try to understand MSSQL Way

User - login map : Try to understand MSSQL Way of handling them

A. What happens when you create a login in MSSQL:

USE master
GO
CREATE LOGIN tsql_testlogin WITH PASSWORD='xxxxxx';
GO

 

>>> A login alone is created under instance security tab. There are no equivalent role/user created in any of the database including master db (which is the default db).

Now we have a login, what is the purpose of it? let us try to login and see if it connect to any of the database and do any operations.

1. The login we created cant connect to the user databases. Amongst system databases, he cant connect to model databases (looks model & user databases a copy of model again exhibit same behaviour).

Access list: 

2. The login can navigate through and select system tables in master db & msdb. Note the tempdb doesnt have any tables unlike model or master or msdb.

3. The login we created can select linked servers.

So why it is so?
1. I found the guest user account is activated in master, tempdb & msdb vs in other dbs it is not.

Activated in master below:                               Deactivated in tsqltestdb2 below:

 

2. So to prove this is the case, I enabled guest user in tsqltestdb2 for connection using the below command...

use tsqltestdb2;
go
grant connect to guest;

3. Now the user is shown without the x mark. Also the user can query the information_Schema.tables; But the user isnt able to list any of the tables under other schema or users. As you see below.

the list of tables as seen by tsql_testloging:           All the tables as seen by a super user:

The tsql_testlogin can even select records from guest owned table:

Note like I mentioned there are tables in other schema, which the login cant see. As you noticed above with super user permission, we can see other schema tables were also there...

4. Now if I revoke the guest user connect permission in master db? What happens?

Bang!!!!!!!!!

The guest user connect permission cant be disabled in master or tempdb!!!!!!!

use master;
go
deny connect to guest;
Msg 15182, Level 16, State 1, Line 20
Cannot disable access to the guest user in master or tempdb.
Msg 15182, Level 16, State 1, Line 20
Cannot disable access to the guest user in master or tempdb.
Msg 15182, Level 16, State 1, Line 20
Cannot disable access to the guest user in master or tempdb.
Completion time: 2023-05-08T14:33:16.9682018+01:00

5. Ok, it didnt mention about msdb, let me deny guest access to msdb.

use msdb;
go
deny connect to guest;
Commands completed successfully.
Completion time: 2023-05-08T14:34:45.2797043+01:00

see below screenshot...

6. Now if I try access the msdb (which was working before) using the tsql_testlogin, it should fail. Let us see...

As we see below...

use msdb;
go
Msg 916, Level 14, State 2, Line 7
The server principal "tsql_testlogin" is not able to access the database "msdb" under the current security context.
Completion time: 2023-05-08T14:39:03.5912681+01:00

I didnt read the MS docs though yet. I was troubleshooting some issues and thus learnt practically what the purpose of several users/roles/schemas.

I will write further chapters on this.

In summary from this excercise... we learnt.
1) As soon as a login is created, by default it is mapped to guest user across the database.
2) The guest user will have no connect permission in any of the dbs by default except - master, msdb & tempdb.
3) The guest user's connect permission cant be denied on master or tempdb.
4) The login user who is mapped to guest user by default can see only guest objects like tables (even information_schema.tables wont list tables from other schemas).
5) Remeber guest user cant create tables in any db by default.
6) Guest user isnt mapped to any roles by default or it by itself isnt a role. But it has a schema.

More to come.

Thank you!