I took a backup of a SQL Server database and restored it as a new database on a different server.
On this new instance I have done the following:
- recreated the login,
- associated the login with the db user using
sp_change_users_login 'UPDATE_ONE', 'username', 'username'
- ensured the users default schema was
dbo
. - ensured the users was a member of the
db_readdata
role. - checked to make sure the user showed up in
sys.dabase_principals
- granted the user View Definition
- granted connect to the user.
When I log in as the user I still do not see the database. They can see other databases but not this one.
What else is there to check?
I ran EXEC sp_change_users_login 'Report'
and this shows no output however running autofix, or update_one
appear to run successfully. The user also shows up under security>Users
.
I am using SQL Server 2008 R2 Developer Edition.
Best Answer
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 seemydatabase
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 statementGRANT 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
-- Backup the database
On Instance B
-- restore the database to instance B
-- recreate the login,
-- fix the user
-- connect to instance B in SSMS as myuser
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