A user must be a fixed server role? 2

[masds]

When I create a login user in SQL server EM, do I need to grant this login user a fixed server role? Is this step neccessary? why? It seems if I don't grant him any role and grant him a database access, he still can access the database.

 

[SQLBill]

Normally you will not grant users system roles.

System roles are built-in roles that allow a user to do 'supervisory' things. Such as: create and alter databases (Database Creators role) - allows user to create or alter ANY database.

Basically, server roles are ADMINISTRATIVE roles and should not be given out freely.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Fixed Server Roles are only necessary if the user in question needs to access Administrative permissions on SQL Server.

Fixed Database Roles exist for the convienence of assigning a pre-set definition of permissions to a SQL user account.

You can create a user account in SQL, ignore the Fixed Server AND Fixed Database roles, only assigning what permissions are necessary. Or you can create new Fixed Database roles for non-admin users that you want a mix of permissions attached to so you don't have to do this work several times over for every user you grant access to.

You can find more detail on these options in Books Online.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[masds]

Well explained, thanks Bill and catadmin!