SQL Server 2012 – Server Roles

http://bit.do/dgxwk Hidden promo page at DataZenit.
To Learn or Teach Linux visit www.theskillpedia.com, A Marketplace for Learners and Trainers. Sysadmin • This role can do anything within SQL Server. • This role completely bypasses all security checks. • This role can do everything any other role can do and more. • This is the most powerful role in SQL Server. Limit its access to only those who absolutely need it. ________________________________________ Bulkadmin • This role allows the import of data from external files. • Typically, this role isn't needed by normal users. • Even being a member of this role isn't enough. You also have to have INSERT rights on the table. • Members of the sysadmin role don't need to be a member of this role. Only use it when you have users or logins who need to do BULK INSERT operations who aren't already members of sysadmin. ________________________________________ DBcreator • This role allows creation of databases within SQL Server. • This is another role that should be rarely used. • It is an ideal role for a junior DBA to give him/her some control over SQL Server, but not the level of permission sysadmin grants. • Some applications will need to be a member of this role if they "roll over" databases as part of their operations. ________________________________________ Diskadmin • This role allows management of backup devices, which aren't used very much in SQL Server any more. • I have never seen this role used in practice because backup jobs are typically automated (or should be). ________________________________________ Processadmin • This is a powerful role because it can kill connections to SQL Server. • This is another role that should be rarely used. • Typically if connections are to be killed off, you want a fully trained DBA handling this, who are typically members of the sysadmin role. ________________________________________ SecurityAdmin • This role controls logins for SQL Server. • This role can grant access to databases within SQL Server. • This role, by itself, cannot define any further security within a database. • This is another good role for junior DBAs when sysadmin is too much. • Because it is a security related role, membership in it should be carefully restricted, especially in production. ________________________________________ Serveradmin • This role manages the SQL Server configuration. • Typically you want DBAs who are members of sysadmin handing configuration. • This is possibly a role you would hand to a junior DBA, but I wouldn't, especially on a production system. ________________________________________ Setupadmin • Setup admin basically gives control over linked servers. • Again, this is not a role I've seen used in practice. • I would limit this sort of configuration change to DBAs who understand the impact, meaning they are already members of the sysadmin role (meaning you don't need this role).