Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

1 Answer
1

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?

Thanks Craig there shouldnt have been any denys used on the user but I tried the grant to no avail. I dont see any missed steps. Is there any way to query what Grants/Denys are in place for a user using the system tables?
–
LumpySep 4 '12 at 12:40

No problem Lumpy. I updated my answer with a nice link to stackoverflow. Hope it helps.
–
Craig EfreinSep 4 '12 at 16:40