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.
Best Answer
First, if you do both:
and
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 roledb_owner
. When you make some user a member ofdb_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 noview any database
permission but still be a member ofdb_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 anysysadmin
(i.e. a member ofsysadmin
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 executingalter authorization
statement:This way you assigned the
LOGIN
username to be the owner of this database. The owner of database is of course a member ofdb_owner
role but not vice versa. DBO will always "see" the database it owns in OE as anysysadmin
will.To your question:
the error you receive tells you that you cannot execute
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 loginusername
can be created in this database. Then if you want your username to be database owner just drop your userusername
from this database and alter authorization to username, this way it will be mapped todbo
.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 todbo
.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:
You don't need to map a login to database only if it is
sysadmin
or databaseowner
(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.