-1

Recently I was asked question - what is the difference between Standard Server roles and Fixed server roles.

I know Fixed Server roles include: Sysadmin, Securityadmin, etc.

I am not sure about Standard server roles.

Thank you.

  • Where do you come across that term ? – Squirrel Jan 8 at 2:50
  • I was being asked the question – Platt Brigante Jan 8 at 3:49
5

I am not sure what your question is. It seems you or the one who has asked you question are a bit confused between these terminologies.

Server-Level Roles

SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)

Fixed server roles are provided for convenience and backward compatibility. Assign more specific permissions whenever possible.

SQL Server provides nine fixed server roles. The permissions that are granted to the fixed server roles (except public) cannot be changed. Beginning with SQL Server 2012 (11.x), you can create user-defined server roles and add server-level permissions to the user-defined server roles.

You can add server-level principals ( SQL Server logins, Windows accounts, and Windows groups) into server-level roles. Each member of a fixed server role can add other logins to that same role. Members of user-defined server roles cannot add other server principals to the role.

Note

Server-level permissions are not available in SQL Database or SQL Data Warehouse. For more information about SQL Database, see Controlling and granting database access.

Fixed Server Roles

Fixed server roles have a fixed set of permissions and server-wide scope. They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed. Logins can be assigned to fixed server roles without having a user account in a database.

Important

The sysadmin fixed server role encompasses all other roles and has unlimited scope. Do not add principals to this role unless they are highly trusted. sysadmin role members have irrevocable administrative privileges on all server databases and resources.

SQL Server does indeed come with several built in fixed server roles. They are:

  • sysadmin
  • bulkadmin
  • dbcreator
  • diskadmin
  • processadmin
  • securityadmin
  • serveradmin
  • setupadmin

The fixed database roles are:

  • db_owner
  • db_securityadmin
  • db_accessadmin
  • db_backupoperator
  • db_ddladmin
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

Kindly refer Microsoft site and other sites for more details on mentioned roles and their details.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-server

https://www.mssqltips.com/sqlservertip/1887/understanding-sql-server-fixed-server-roles/

https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/

Hope above helps.

  • Thank you so much for the information – Platt Brigante Jan 8 at 14:20
1

There is no such thing as "Standard Server Roles"

There is only "Fixed Server Roles"

Possible that someone you know mistakenly calls "Fixed Server Roles" by "Standard Server Roles", but that is wrong

Not the answer you're looking for? Browse other questions tagged or ask your own question.