August 21, 2007 at 4:47 am
Hi all,
I created a DB (called Iris) and need to give an NT local group ownership of the DB. Now, at the moment my own personal-named login is listed the login for DBO in the Users list, and all my permissions boxes are ticked.
So, I executed a sp_changedbowner 'OurDomain\OurNTLocalGroup', and the system claims the 'OurNTLocalGroup' login does not exist - but it DOES appear twice in EM, in both the Security-logins section and in the IRIS-Users section. (However, I can successfully change the dbo to a couple of other logins)
I then went to IRIS-Users, clicked the Db_owner box for the database role, but nothing - his permissions don't change. I added OurNTLocalGroup to the DD Readers/Writers roles, nothing changed.
Anyone know what I need to do to allocate the group as the DBO?
Thanks,
Jaybee.
August 21, 2007 at 4:52 am
Jaybee
Try removing OurNTLocalGroup as a user from the database. Then execute sp_changedbowner 'OurDomain\OurNTLocalGroup' and that should change the owner for you.
John
August 21, 2007 at 5:31 am
Hi John,
Didn't work. Went into EM and deleted the OurNTLocalGroup user. No difference, it still says,
"Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line 33
The login 'OurDomain\OurNTLocalGroup' does not exist.
August 21, 2007 at 5:42 am
Jaybee
I think you need to add 'OurDomain\OurNTLocalGroup' as a login before you can make it the owner of the database. I'm guessing that what happened is that the login used to exist but was deleted and so you now have an orphaned user in your database. Either that or your database was restored from another server that does have it as a login.
Hope that makes sense
John
August 21, 2007 at 6:01 am
A windows group can not own a database. See sp_changedbowner in BOL. A possible work around would be to add the desired group to the DB as a user then set the permissons to db_owner. This gives the user/group extended permissions. Take a look at "db_owner" and "database owner" in BOL to get a better understanding.
Good Luck,
John
August 21, 2007 at 6:11 am
Neither one. The Login existed long before I joined this company a few months ago, and I only created the DB yesterday.
Here's the proof:
exec sp_adduser 'OurDomain\OurNTLocalGroup'
Server: Msg 15007, Level 16, State 1, Procedure sp_adduser, Line 15
The login 'OurDomain\OurNTLocalGroup' does not exist.
Like I said above, EM already shows (Under Security =>Logins) the local group. Just for sh1t and giggles I tried to introduce the group from Active Directory as a brand new login - the error message said, "login already exists".
Appreciate your help, but has anyone actually worked around this problem before?
August 21, 2007 at 6:16 am
Did that at the start - nothing.
August 21, 2007 at 6:30 am
If the login does not exist on the server, then you will need to add it. For an orphaned server, the best approach is to revoke the login for the database in question and to re-grant access to it, like so:
EXEC
sp_revokedbaccess 'OurDomain\OurNTLocalGroup'
EXEC
sp_grantdbaccess @loginame = 'OurDomain\OurNTLocalGroup'
Paul
August 21, 2007 at 2:57 pm
The windows group does not need to be the database owner, it just needs to be in the db_owner role.
The best this is to make sa the owner of the database:
use MyDB
exec sp_changedbowner @loginame = 'sa'
Then add your group as a login (if it isn't already), add it as a user in the database (if it isn't already), and then add it to the db_owner database role in the database.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply