June 28, 2007 at 8:14 am
Under Security/Logins I find my user name. Using Database Access tab, I highlight a database. I uncheck "db_owner" in bottom box. I attempt to save it and get:SQL server error 15405: cannot use the reserved user or role name 'dbo'.
As you can see, I'm not, I'm attempting to remove it from my name.
A brief history:
All objects in this database have owner: dbo.
ME, the previous Sysadmin, User Domain\myname was assigned dbo of several databases at the Security\logins level. Security policy has changed and now we need to change to db_dataread, db_datawrite and execute on all stored procs and functions for data manipulation.
In order to test the new concept, I must first remove Domain\myname as dbo, sysadmin and only grant rights (db_dataread, db_datawrite and execute on all stored procs and functions) to the database.
I've Deleted my name as a user from at the database level, as well as remove myself as a SQL Server login.
In effect, 'Domain\myname' a user, having a login, has been deleted.
Now, to test my new permissions, from the SQL Server level, I add myself again, using the same domain\myname(as we are going to windows authentication as this is necessary for the test).
As soon as I create myself in Security/Logins and attempt to close the dialogue box, I get the message "It has been detected that this login has permissions in specific database(s) - the login will have access to these databases now."
It restores me as dbo in all the databases I was in prior to deletion. I cannot uncheck dbo in Database Access and save.
Note: I have stopped and re-started the sql server service.
Can anyone shed light here?
June 28, 2007 at 9:29 am
Hmmm .. think you might benefit from a good read of BOL or a suitable training course!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 28, 2007 at 2:24 pm
You might get better feedback if you post in one of the SQL subfolders instead of the Notification Services area.
June 29, 2007 at 5:34 am
Try running orphan user script in each and every user databases. This will drop and clear the anamolies in the user-login conflicts. As you are a sysadmin, you can still be in the group and you will have all the privileges on the system. I want understand why you want to be a user again. Recreate all the database objects with "dbo".
-- Orphan user Security Fix:
DECLARE
@username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null and [name] in (select [name] from master.dbo.syslogins)
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
GO
Thanks,
.A.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply