Take the 2-minute tour ×
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.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

enter image description here

Show all databases connected as myuser to InstanceB

enter image description here

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

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

share|improve this answer
    
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? –  Lumpy Sep 4 '12 at 12:40
    
No problem Lumpy. I updated my answer with a nice link to stackoverflow. Hope it helps. –  Craig Efrein Sep 4 '12 at 16:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.