SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Public Role – a potential high risk security concern for DBAs

The public role is a special fixed-database role, which exists in every SQL Server database. The public role is different from all other database-level roles because in SQL Server, every database user is automatically assigned to the public database role, and you cannot remove public database role from SQL Server database. Although, permissions can be granted, revoked, and denied to the public role, however, granting or denying permissions to this role is not considered a best practice. This is because, when public role has permissions to do something, then every user in the database automatically gets this permission, which indeed is a high risk to database security. In addition this, when users have not been explicitly granted or denied permission on a securable object then users automatically inherits the permissions of public role. That is why, as per Microsoft Books Online and SQL Server Security best practice white paper, it is recommended to periodically review privileges granted to public role, and revoke any unnecessary privileges assigned to this role.

Checkout my article (The Public role – a potential high risk security concern for DBAs) on SSWUG.org, in which I discussed the security issues associated with the public database role, and how you can quickly find and remove privileges granted to the public database role in each database.

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...