Sql-server – User in multiple windows groups mapped to sql logins

I went through the sames steps as you described. Restoring a database from one server to another and then fixing the user. I didn't give any additonal permissions though, as you did with GRANT VIEW DEFINITION.

If however, I execute DENY VIEW ANY DATABASE to PUBLIC on Instance B, myuser can no longer see mydatabase in SSMS, but he can connect to mydatabase and run queries either way.

Without DENY VIEW ANY DATABASE , myuser on Instance B, was able to see the restored database. I then used the opposite statement GRANT VIEW ANY DATABASE TO public to give that permission back.

Is there anything here in this test that you did that I didn't?

Is the user on the server with the restored database still able to connect to that database and run queries?

On Instance A

-- Create the mydatabase database

CREATE DATABASE mydatabase 
CREATE TABLE mytable(t INT)
INSERT INTO mytable (t) VALUES (1),(2) 

create the login myuser, with default schema dbo and is member of db_datareader.

USE [master]
GO
CREATE LOGIN [myuser] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[mydatabase], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [mydatabase]
GO
CREATE USER [myuser] FOR LOGIN [myuser]
GO
USE [mydatabase]
GO
ALTER USER [myuser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [mydatabase]
GO
EXEC sp_addrolemember N'db_datareader', N'myuser'
GO

-- Backup the database

BACKUP DATABASE mydatabase to disk = 'C:\MSSQL\Backup\mydatabase.bak'
GO

On Instance B

-- restore the database to instance B

RESTORE DATABASE mydatabase FROM DISK = 'C:\MSSQL\BACKUP\mydatabase.bak'
WITH FILE=1,
MOVE 'mydatabase' TO 'C:\MSSQL\mydatabase.mdf',
MOVE 'mydatabase_log' TO 'C:\MSSQL\mydatabase_log.mdf',
RECOVERY, NOUNLOAD, STATS=10

-- recreate the login,

CREATE LOGIN [myuser] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[mydatabase], CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF

-- fix the user

USE [mydatabase]
sp_change_users_login 'UPDATE_ONE', 'myuser', 'myuser'

-- connect to instance B in SSMS as myuser

use mydatabase
select * from mytable

t
1
2

Connect as myuser to InstanceB using mydatabase

Show all databases connected as myuser to InstanceB

I found this on answer on stackoverflow which provides a query that returns users and their permissions for a given database:

https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database

The idea of CREATE USER ... WITHOUT LOGIN isn't so you can map it later with ALTER USER ... WITH LOGIN (SQL Server simply refuses, as youhave found).

Instead, the idea is for application developers to change the security context before issuing SQL statements, by first doing EXECUTE AS USER = '<user>' WITHOUT REVERT to shed permissions.

This kind of gets you halfway to where you want to be. You ultimately still need to map some SQL Server logins to some SQL Database Users, but you reduce your need to know in advance what they will be, because you declare access-control permissions against the 'WITHOUT LOGIN' user.

See:

1. User without login purpose
2. SQL SERVER – Importance of User Without Login – T-SQL Demo Script