Thanks for your advice. I first noticed the logins in Management
Studio. I've done a bit more digging around and found some other
unexpected security objects.
Running sp_helplogins gives the 3 logins previously mentioned and 4
others I wasn't expecting:
##MS_AgentSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLResourceSigningCertificate##
These also appear in the sys.server_principles catalog.
I'm comfortable with leaving these as they are, but I guess I always
like to "know" what unexpected database objects are for, how they work
etc. In addition, I have a centralised security system that records
DBMS server and DB permissions for multiple DBMS types. This is used by
my batch process that manages DBMS/database security each day. At the
moment for this one SQLServer 2005 server it thinks it should remove
these logins, which is a nuisence. I'll have to alter the batch job to
take account of these logins.
Thanks again.
Laurence Breeze,
DBA Team Leader,
The Open University.
UK
[quoted text, click to view] Erland Sommarskog wrote:
> Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes:
>
>>Where are you seeing those Laurence?
>>
>>Permissioning for SQL Server 2005 is done through groups; are you sure
>>these aren't actual AD groups?
>
>
> I got the corresponding on my server (in sys.server_principals). And are
> definitely not any AD groups, as this is a workgroup machine.
>
> But they are indeed groups.
>
> I would not drop them. That could end in tears.
>
>
Laurence Breeze (i.l.breeze@open.ac.uk) writes:
[quoted text, click to view] > Running sp_helplogins gives the 3 logins previously mentioned and 4
> others I wasn't expecting:
>
> ##MS_AgentSigningCertificate##
> ##MS_SQLAuthenticatorCertificate##
> ##MS_SQLReplicationSigningCertificate##
> ##MS_SQLResourceSigningCertificate##
>
> These also appear in the sys.server_principles catalog.
It would a bad idea to delete these. These logins are mapped to
certificates and it is not possible to log in as these. (You see this
in the type_desc column.)
Certificates mapped to logins are used in scheme which makes it possible
to grant permissions to users through an SQL module. Exactly what these
four predefined login are used for I don't know, but dropping them
could cause problems the day you would have needed them.
Overall, there is a lot of new stuff around security in SQL 2005.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
