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.
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
CREATE LOGIN [myuser] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[mydatabase], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [myuser] FOR LOGIN [myuser]
ALTER USER [myuser] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember N'db_datareader', N'myuser'
-- Backup the database
BACKUP DATABASE mydatabase to disk = 'C:\MSSQL\Backup\mydatabase.bak'
On Instance B
-- restore the database to instance B
RESTORE DATABASE mydatabase FROM DISK = 'C:\MSSQL\BACKUP\mydatabase.bak'
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
sp_change_users_login 'UPDATE_ONE', 'myuser', 'myuser'
-- connect to instance B in SSMS as myuser
select * from mytable
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: