April 21, 2006 at 4:54 am
Hello all.
I am new to this role and am currently planning user set up on our database servers. Before I could move on, I made some observations on which I would be needing help.
1. Many databases have no logins mapped to dbo user. When I click on Users in Database, I see the dbo user with nothing appearing in Login Name. It is the same in production, staging and development environment. We have set up a new development environment. What are the implications? Should I leave it like that or map the dbo to sa using sp_change_dbowner. Also, why did it happen in first place. Any idea? Or should I assume that nothing means sa is by default mapped to dbo.
2. Digging further in this, I ran the script which I caught from this site:
SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
WHERE u.name = 'dbo'
GO
It gave me list of dbs with unmatched dbos but when I exected sp_change_users_login 'report', it did not return any records for the ones with unmatched dbos. I am a bit confused.
3. Is it a good idea to have sa as dbo user. Or we can have another user with sysadmin role be the dbo.
4. If there are two users with sysadmin role in same database, only one gets to be dbo. This seems a bit strange.
5. We have a team working on application at front end and they need to set up databases for their platform. These databases would be for their software and not related to application. I need to set up an account for them so that they can create databases and do all operations on them. I preferred creating a login with DB_Creator fixed server role. When they will create databases using this login, it will become dbo. Is that fine? I ask this because dbo user would not be having sysadmin rights or we should only have sysadmin roles set to dbo user?
I would really appreciate if you guys can take out some time and help me with your experience.
Looking for some helpful advice.
Thanks,
Madiha.
April 21, 2006 at 6:50 am
I tend to have the SA as the explicit dbo user. It is the one login that is common across all servers.
You also have the db_owner role which multiple users can be a member of. They do not have to be sysadmins at all, infact you can have someone whose only rights are as db_owner in a single database
April 21, 2006 at 7:10 am
Thanks for the reply.
However, my question is that if someone other than dba has to create the dbs using batch files, you would not want them to have sa rights. In that case, what login you would create for them. The case in which you give them db_owner rights is when the database is already there and you are creating a user for them.
Any thoughts?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply