May 28, 2013 at 4:09 am
I have a server where there are logins created that are mapped to dbo in a database.
I want to change then to map to a user of the same name but I can see a way without dropping the user (dbo) which I can't.
CREATE USER MyLogin FOR LOGIN MyLogin
reports
The login already has an account under a different user name.
ALTER USER isn't any use; I don't want to rename the dbo user.
I also have logins mapped to a user with a different name that I don't want to drop so the dbo user example is a special case of a more generic problem I have.
There must be a simple way, surely?
May 28, 2013 at 6:03 am
You have to change the owner of the database to another account (like 'sa'). Because "MyLogin" is currently the owner it is mapped to the "dbo".
May 28, 2013 at 6:19 am
it sounds like orphaned users to me, is that right?
for SQL users(not windows users)
if you restore a database from a different server, the user "Bob" on my server is technically different than the user 'Bob' on your server (the sid in sys.database_principals)
I use this to help track those issues down and fix them when needed:
SELECT
CASE
WHEN svloginz.name is not null and dbloginz.sid <> svloginz.sid
THEN '--Login Exists but wrong sid: remap!
ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(svloginz.name) + ';'
WHEN svloginz.name is null
THEN '--USER exists, but no matching login found:
--CREATE LOGIN ' + quotename(dbloginz.name) + ' WITH PASSWORD=N''NotARealPassword'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;
--ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(dbloginz.name) + ';'
END
from sys.database_principals dbloginz
LEFT OUTER JOIN sys.server_principals svloginz
on dbloginz.name = svloginz.name
WHERE dbloginz.type IN ('S')
AND dbloginz.name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys')
Lowell
May 28, 2013 at 6:40 am
RichardBo (5/28/2013)
I have a server where there are logins created that are mapped to dbo in a database.
Only one login can be mapped to dbo in any particular database. This becauser a database can only have one owner, and also because any login can only map to one user in that database, and vice versa.
I want to change then to map to a user of the same name but I can see a way without dropping the user (dbo) which I can't.
As HanShi suggests, you need to change the database owner. Use sp_changedbowner to do this. Once you've done that, you can use CREATE USER to map the former database owner to a new user in the database.
John
June 1, 2013 at 5:07 pm
RichardBo (5/28/2013)
I have a server where there are logins created that are mapped to dbo in a database.
When the Login is a member of sysadmin this happens regardless of whether the Login has a User in the database or not. That might explain some of what you're seeing if some of these Logins are members of sysadmin.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 3, 2013 at 2:18 am
opc.three (6/1/2013)
When the Login is a member of sysadmin this happens regardless of whether the Login has a User in the database or not.
But dbo is a user in the database. It's certainly true that a member of sysadmin will have permissions in a database as if it were dbo, but dbo can only be mapped to (a maximum of) one login.
John
June 3, 2013 at 7:37 am
John Mitchell-245523 (6/3/2013)
opc.three (6/1/2013)
When the Login is a member of sysadmin this happens regardless of whether the Login has a User in the database or not.But dbo is a user in the database. It's certainly true that a member of sysadmin will have permissions in a database as if it were dbo, but dbo can only be mapped to (a maximum of) one login.
John
Right. And that Login is sa and that cannot be changed. When looking at things through SSMS GUI the mappings for non-sa Logins might appear to be mapped to the dbo User when in reality it's not true. This is because a Login has to enter the database as someone, so the trick is played to haventhem enter as dbo. Same with Database Owners. This seems to be true regardless of whether the Login has a User in the database. The CURRENT_USER function shows this behavior.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 3, 2013 at 8:30 am
opc.three (6/3/2013)
Not sure I understand...
Right. And that Login is sa and that cannot be changed.
When I create a new database from my own login, which has access through its membership of a Windows group, dbo is not mapped to any user. Here are the results of sp_helpuser 'dbo':
[font="Courier New"]UserName GroupName LoginName DefDBName DefSchemaName
dbo db_owner NULL NULL dbo[/font]
If I then change the database owner to sa with sp_changedbowner 'sa', I get this:
[font="Courier New"]UserName GroupName LoginName DefDBName DefSchemaName
dbo db_owner sa master dbo[/font]
If I change the owner to a login called TestAdmin, I get this:
[font="Courier New"]UserName GroupName LoginName DefDBName DefSchemaName
dbo db_owner TestAdmin master dbo[/font]
When looking at things through SSMS GUI the mappings for non-sa Logins might appear to be mapped to the dbo User when in reality it's not true.
Are you sure you're not looking in the Default Schema column instead of the User column? That catches me out from time to time.
Same with Database Owners. This seems to be true regardless of whether the Login has a User in the database.
Are you referring to members of the db_owner database role? To be a member of that, a login needs to have a user in the database.
This is because a Login has to enter the database as someone, so the trick is played to haventhem enter as dbo....The CURRENT_USER function shows this behavior.
Yes, you're quite right. If I log in as a sysadmin and do CURRENT_USER in a database that doesn't have a mapped user for my login, it shows dbo.
So, in summary, do you agree with this?
(1) The login that owns the database is mapped to dbo
(2) Logins that are members of sysadmin and do not have a mapped user in the database connect to it as dbo
(3) All other logins that have a user in the database connect as that user
(4) No logins apart from those mentioned above have access to a particular database (remember this is a SQL Server 2005 forum and so we're not considering contained databases)
John
June 3, 2013 at 10:46 am
John Mitchell-245523 (6/3/2013)
So, in summary, do you agree with this?(1) The login that owns the database is mapped to dbo
Yes.
(2) Logins that are members of sysadmin and do not have a mapped user in the database connect to it as dbo
Yes.
(3) All other logins that have a user in the database connect as that user
No. Even if there is a User in the database for the Login, if they are in the sysadmin Role they are mapped into the database as the dbo User.
(4) No logins apart from those mentioned above have access to a particular database (remember this is a SQL Server 2005 forum and so we're not considering contained databases)
Yes.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 4, 2013 at 2:35 am
Wow! You're right on 3. We're eventually in agreement on everything.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply