February 26, 2008 at 8:39 am
When I ran the sp_OrpanedUsers found here it works in SQL Server 2000 and was able to remove those users.
When I run EXEC sp_change_users_login 'Report', It only gives me 2. When I try to remove the orphaned users, it tells me it owns a schema. How do I find out what schema it owns, and how do I remove or get the correct list of orphaned users to remove. I also ran
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
and that includes the sys user... dangerous...
¤ §unshine ¤
February 26, 2008 at 9:00 am
You can use this query to list all the objects owned by EUR\ld5594 ID,
Select name from sysobjects where uid=user_id('EUR\ld5594')
you need to replace the login name with yours. After that you can change the object owner to some other ID using, sp_changeobjectowner stored procedure.
[font="Verdana"]- Deepak[/font]
February 26, 2008 at 9:06 am
I get nothing returned when I run that... strange. I receive a list of orphaned users, try to delete, can't because it owns schema, but when I run the select script with the user id, I get nothing....
¤ §unshine ¤
February 26, 2008 at 9:07 am
Here is the exact error I get when I try to delete.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for User 'asava'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
-----------------------------
BUTTONS:
OK
------------------------------
¤ §unshine ¤
February 26, 2008 at 9:10 am
Found something that works!
Thanks all!
1) Expand Schemas(should be like a folder under Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.
¤ §unshine ¤
February 28, 2008 at 9:06 am
Did you ucheck if that schema owned any user types.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply