RE: master DB users
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 18 May 2009 15:09:20 -0700
Yousef Alsayed (josppy@xxxxxxxxxxxxxxxx) writes:
1. In my company I have a standard that Restric Access to Master Database
(No user other than DBA should be allowed to access the master database).
I as a security engineer I have to check if the DB admins comply to this
standard.
2.I want to ensure that only database administrator has access to the
Master database.
This is actually not achievable, because the guest user is always enabled
in master, and you cannot disable it, I believe.
However, you are perfectly right in that no server logins should map
explicitly to database users in master, except those that SQL Server
add on its own. On my SQL 2008 instance, I find these when I run:
select * from master.sys.database_principals
public
dbo
guest
INFORMATION_SCHEMA
sys
##MS_PolicyEventProcessingLogin##
##MS_AgentSigningCertificate##
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter
You may see others on your server, because things like replication
may add more users. But their names should make it clear that they
are system-defined.
Next you can run this query:
select o.name, o.schema_id, *
from master.sys.database_permissions dp
left JOIN master.sys.all_objects o ON dp.major_id = o.object_id
where grantee_principal_id in (0, 2)
order by o.schema_id, o.name
This query lists all permissions granted to public and guest in
master. On my server, it returns 1841 objects, of which the verymost
is in schema 4, the sys schema. Those that are not in the sys schema
are still objects that ship with SQL Server, and which all users
need SELECT or EXECUTE permission to. Thus you should note revoke these
permissions.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- master DB users
- From: Yousef Alsayed
- RE: master DB users
- From: Mark Han[MSFT]
- RE: master DB users
- From: Yousef Alsayed
- RE: master DB users
- From: Mark Han[MSFT]
- RE: master DB users
- From: Yousef Alsayed
- master DB users
- Prev by Date: Re: check blank SA Password ?
- Next by Date: RE: master DB users
- Previous by thread: RE: master DB users
- Next by thread: RE: master DB users
- Index(es):
Relevant Pages
|
