Sql-server – dbcreator role member not able to access database it just restored

restorerolesql serversql-server-2008sql-server-2008-r2

I have added a new windows account [PROD\apdf] and given it the dbcreator role.

The user initiates a restore from an app server.
The database restores correctly. Under database properties it shows the prod\apdf user as the database owner. However, that user is not granted access to the database. The sysadmins and dbcreators are added to security on the new database, but not the one it was restored by. When pulling up the dbcreator role list, the [PROD\apdf] user is on that list.

I have made sure that the account is not orphaned and it shows it is a member of the dbcreator role using:

EXEC sp_helpsrvrolemember 'dbcreator';

I have seen an article that indicates this may be by design, but I wanted to see if anyone else had an answer about this.

Best Answer

After the database is restored, a member of the sysadmin or securityadmin server roles will need to alter the authorization (ownership) of the database to the login:

ALTER AUTHORIZATION ON DATABASE::<name of database> TO [PROD\apdf];

I can verify the restore operation does not seem to correctly grant ownership through the below MCVE.

create a login and make it a member of the dbcreator server role:

CREATE LOGIN backup_restore_test
WITH PASSWORD = 'asdfousd^f89aw09e8r90qawer$udfjoia'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;

ALTER SERVER ROLE dbcreator ADD MEMBER backup_restore_test;

Take a backup of the model database, to restore later:

BACKUP DATABASE model 
TO DISK = 'C:\temp\model.bak'
WITH COPY_ONLY;

RESTORE DATABASE newmodel
FROM DISK = 'C:\temp\model.bak'
WITH MOVE 'modeldev' TO 'C:\temp\modeldev.mdf'
    , MOVE 'modellog' TO 'C:\temp\modellog.ldf'
    , RECOVERY;

This shows the backup_restore_test login is the owner of the database:

SELECT d.name
    , sp.name
FROM sys.databases d
    INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
WHERE d.name = 'newmodel';

However, this fails:

SELECT *
FROM newmodel.sys.tables

The error:

Msg 916, Level 14, State 1, Line 14
The server principal "backup_restore_test" is not able to access the database "newmodel" under the current security context.

Switch back to a sysadmin login, and run this:
```
ALTER AUTHORIZATION ON DATABASE::[newmodel] TO [backup_restore_test];
```
Now, this works, when logged in as the [backup_restore_test] login:
```
SELECT *
FROM newmodel.sys.tables
```

Unless I'm overlooking something, the backup_restore_test login should have access to the database as soon as it is restored since it owns the database at that point. I've verified this behavior on SQL Server 2012, and SQL Server vNext

Related Solutions

Sql-server – Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights

to relink the login to the correct id you will have to run (I assume that the login is appuser, and the databas user appuser):

alter user appuser with login = appuser ;

From BOL:

To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema. Requires CONTROL permission on the database to remap a user to a login

As you explained that you have the db_owner role on the database, it should work.

Sql-server – User can’t see database in SQL Server 2008 R2

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:

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

Best Answer

Related Solutions

Sql-server – Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights

Sql-server – User can’t see database in SQL Server 2008 R2

Related Question