SMO : The login already has an account under a different user name, when calling TransferData()

Question

I need to implement the following requirement. I have setup a remote sql server express, certain users shall be able to create databases on remote server and assign other users to have access on it (read,write). Users shall be able to see only the databases created by them or in which they have rights.

First I have created an empty database on remote server => dbo for new database is x, then I've created a different login with:

USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Then deny view to any database:

USE master;
GO
DENY VIEW ANY DATABASE TO [username]; 

Then create new user for login:

USE dbName; 
CREATE USER [username] FOR LOGIN [username] ;
GO

Give db owner role:

EXEC sp_addrolemember 'db_owner', [username]

Give permission to see database (here is a little bit tricky because in ssms it can view all the databases but it can have access only on the mapped ones)

USE master GRANT VIEW ANY DATABASE TO [username];

Now on my given database I have 2 users one is username and the other is dbo which is mapped on the user that created the database and is different form username.

After that I'm using SMO to transfer this databases from remote server to localhost and while executing transferDatabase.TransferData(); I'm getting the following error "The login already has an account under a different user name." When I create the transfer instance I set also the property CopyAllUsers= true.

How can I fix this?

I've found out that if i make the following changes

USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Then deny view to any database:

USE master;
GO
DENY VIEW ANY DATABASE TO [username]; 

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username];
GO

Now username sees in ssms only the databases on which is mapped and the transfer will probably work. But now I cannot distinguish between the first creator of the database and the recently granted user. For one database I should grant dbowner permissions to more than one user.

So if then I create another login and I execute:

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::dbName TO [anotherUser];
GO

Now username can not access the database.

Answer 1

First, if you do both:

GRANT VIEW ANY DATABASE TO [username];

and

DENY VIEW ANY DATABASE TO [username];

the DENY still wins, so there was no sense to grant it, this is also the default permission that every new login has so there was no need to grant it again.

Second, you don't see the difference between database principal dbo and any member of the fixed database role db_owner. When you make some user a member of db_owner this user has all possible permissions in this database. But this has nothing to do with the fact "to see some database in Object Explorer".

To "see" any database in OE, login should have view any database permission. Login and not user. So your login can has no view any database permission but still be a member of db_owner role, in this case user has all the permissions in that database but it cannot "see" any database in OE.

Dbo database principal is a special principal where any sysadmin (i.e. a member of sysadmin server role) is mapped, and the OWNER of database is also mapped to dbo. The owner of database is one and only one login that created this database or was assigned to be database's owner by executing alter authorization statement:

ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username]

This way you assigned the LOGIN username to be the owner of this database. The owner of database is of course a member of db_owner role but not vice versa. DBO will always "see" the database it owns in OE as any sysadmin will.

To your question:

the error you receive tells you that you cannot execute

CREATE USER [username] FOR LOGIN [username]

Because the LOGIN username is already mapped to this database, as dbo. So you cannot re-map it to another username.

If you want your script to run without errors change the database owner to any other login, for example to sa login, this way the login username can be created in this database. Then if you want your username to be database owner just drop your user username from this database and alter authorization to username, this way it will be mapped to dbo.

Alternatively, if you already know that you want this user to be database owner, drop the user username from your database prior to transfer and it still be there in the database as it is mapped to dbo.

So if then I create another login and I execute:

USE master; GO ALTER AUTHORIZATION ON DATABASE::dbName TO [anotherUser]; GO Now username can not access the database.

This is because as I explained above, database OWNER can be only one login at a time. If you want your ex-owner to access this database you should map it:

USE dbName; 
CREATE USER [username] FOR LOGIN [username] ;
GO

You don't need to map a login to database only if it is sysadmin or database owner (which, I repeat it, can be only one). Any other login should be mapped to database (create user for login) in order to access it.

Answer 2

I'm sure that sepupic covered it in the elaborate answer. So, just to phrase it short:

There are only two ways to "hide" databases from logins:

DENY VIEW ANY DATABASE. Disadvantage is that you see no databases, not even the ones you can access. With the exception of those that you (we're talking about the login here) own. The owner_sid column in sys.databases is what counts.

Use contained authentication (set database containment to PARTIAL, and you now can get into your SQL server without a login. You are sandboxed to the database you specified in the connection string.