December 31, 2009 at 8:49 am
Hello All,
I have a user in each of my databases called 'sys'. This user has no login. Recently we had another firm come in and do a health check on our SQL Server 2005 instance. They came back with the conclusion that the 'sys' user is an orphaned user and can be removed.
However, when I right click on the user in a given database, I don't have the option to 'delete.' When I do 'Script User as' > 'DROP to' > 'New query editor window' I see the following code:
USE [reporting]
GO
/****** Object: User [sys] Script Date: 12/31/2009 08:44:16 ******/
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'sys')
DROP USER [sys]
When I run that, I get the following message:
Msg 15150, Level 16, State 1, Line 3
Cannot drop the user 'sys'.
In Management Studio, I double-clicked on the sys user and I don't see any owned schema and I only see a General tab, not Securables or Extended Properties.
Were we given bunk information? Can this user not be deleted?
Thanks!
December 31, 2009 at 9:17 am
Per books online (under sys.database_principals)
INFORMATION_SCHEMA and sys
Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. These entities are required by SQL Server. They are not principals, and they cannot be modified or dropped
You were given incorrect information. sys, while it appears as a user, is not supposed to be associated with a login.
At this point I might be questioning whoever did the health check as to the source of their advice....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2009 at 9:19 am
Perfect! Thank you Gail, I wasn't able to dig up anything using Google but completely spaced out BOL. DOH! Tks again.
December 31, 2009 at 9:20 am
You cannot drop " sys " user from any database since it owns sys schema. Though this user " sys " is not assigned any role by default.
execute this to get more idea.
select * from sys.sysusers where name like 'SYS%'
GO
select * from sys.schemas where principal_id = user_id('sys')
GO
select * from sys.objects where schema_id = schema_id('sys')
GO
SQL DBA.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply