SQL Repository
  • Home
  • Articles
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Code Snippets
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Interview Questions
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • How To
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Contact





Server and Database Roles in SQL Server

On 18 Apr, 2015
MS SQL DBA
By : Charith Silva
No Comments
Views : 788

 

SQL Server use role based security, which allows Database Administrators to assign particular permissions to users and groups. SQL server has a set of fixed roles at both the server and database levels and user-defined roles can also be created at both server and database   levels. The fixed roles have a specified set of permissions but user-defined roles have a user-defined set of permissions applied to them.

 

Server roles

Server roles have permissions that span the entire server instance. The most powerful server role is the sysadmin role. You should be cautious about assigning logins to this role as members of this role have complete access to the entire server.

 

The following table shows the server-level roles and their capabilities.

(Source : technet.microsoft.com)

Server-level role name Description
sysadmin Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.
processadmin Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin Members of the setupadmin fixed server role can add and remove linked servers.
bulkadmin Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin The diskadmin fixed server role is used for managing disk files.
dbcreator Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users.

 

 Database roles

The following table shows the fixed database-level roles and their capabilities. Fixed database roles are defined at the database level and exist in each database. These fixed database roles are similar to concept to the fixed server roles but they relate to access to database objects or access to the database itself, rather than access to all databases on the server.

(Source: technet.microsoft.com)

Database-level role name Description
db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

 

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Google+ (Opens in new window)

Related



Previous Post Next Post 

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.


Number of Posts : 87
All Posts by : Charith Silva

Leave a Comment

Click here to cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">





  • Popular
  • Recent
  • Database stuck in “Restoring” state

    10223 views
  • Script to Check TempDB Speed

    3061 views
  • Find the modified date of SQL Server Agents Jobs

    3045 views
  • Log shipping Alerts failing to send emails

    2332 views
  • PING all the Linked Servers and get a status report

    2273 views
  • Moving the tempdb database

    27 Jan, 2016
  • Script to Check TempDB Speed

    14 Jan, 2016
  • SQL Server buffer pool

    05 Nov, 2015
  • Log shipping Alerts failing to send emails

    04 Nov, 2015
  • View queries waiting for memory grant

    21 Oct, 2015

Useful links

  • Books Online for SQL Server 2012
  • Developer Reference for SQL Server 2014
  • Download SQL Server
  • Installation for SQL Server 2012
  • Microsoft Virtual Academy
  • SQL Server Online Training
  • Transact-SQL Reference
  • Tutorials for SQL Server 2012

Tags

.CSV 70-461 AdventureWorks 2012 ALL ANY CAST Chinook Database Code Snippet CONVERT CTE dataset datasource Dates DATETIME divide by zero Duplicates Exam EXCEPT expressions FORMAT IF Import Indexes INTERSECT Jobs NULLIF REBUILD Recursive CTE REORGANIZE ROW_NUMBER() Schedules Sequence SOME sp_stop_job SQL Server 2012 SQL Server Agent SSIS SSRS T-SQL Tally Table T_SQL UAC Permissions Error UNION UNION ALL

Recent Comments

  • Rudnei Silva on Log shipping Alerts failing to send emails
  • johnson Welch on Database stuck in “Restoring” state
  • Neil on Database stuck in “Restoring” state
  • Mark Gribler on MS SQL Database Administrator Interview Questions – Part 4

Google Analytics Stats

Latest Tweets:

  • 4 years ago Attended @SQLSatMcr yesterday - it was amazing! Roll on @sqlsatcambs! Won some Beats Headphones courtesy of @SQLDBApros - thanks guys! :)
  • 4 years ago Looking forward to attending @SQLSatMcr - its too far off though!!!
  • 4 years ago Simple Post: WhoIsActive SPROC: http://t.co/LZvQUaeapK
  • 4 years ago POST: Index REBUILD or Index REORGANIZE: http://t.co/h3L0N37vw4
  • 4 years ago How to Ping all Linked Servers: http://t.co/Q2QxusrKjO
  • 4 years ago For beginners - T-SQL Divide by Zero Error: http://t.co/BBhgoH5hK9

© Copyright 2015 SQL Repository. All Rights Reserved by SQL Repository