Auditing Your SQL Server Environment, Part II (Reviewing Role Memberships)

Roles and Replication

sysadmin This role can enable, modify, or drop distributors, publishers, and subscribers; create, drop, or modify a publication and its properties; create or delete a push or pull subscription; update a PAL (publication access list); enable snapshots for FTP downloading using the Internet; configure agent profiles; monitor replication agents; configure agent profiles; cleanup replication tasks; and schedule replication jobs.
db_owner This role can create or drop, create or modify a publication or its properties; create or delete a pull or push subscription; update a PAL; enable snapshots for FTP downloading using the Internet; cleanup replication tasks; and schedule replication jobs.
 
 

Managing SQL Server Fixed Roles

Several objects exist to aid in the management of fixed SQL Server roles. While you cannot drop fixed roles or drop default role permissions, you can add and delete role members (except for the public database role) and view the permissions assigned to the roles.
 

Fixed Server Roles

sp_addsrvrolemember
 System stored procedure that adds a login as a member of a fixed server role. Permissions default to members of the sysadmin server role that can add members to any fixed server role, and members of a fixed server role to add members only to the same fixed server role.
sp_dropsrvrolemember
 System stored procedure that removes a SQL Server login or a Windows NT user or group from a fixed server role. Permissions default to members of sysadmin fixed server role to remove members of any server role and members each server role that can remove other members of the same server role. Permissions are not transferable.
sp_helpsrvrolemember
 System stored procedure that returns information about the members of a SQL Server fixed server role.
sp_srvrolepermission
 System stored procedure that returns the permissions applied to a fixed server role.
sp_helpsrvrole
 System stored procedure that returns a list of the SQL Server fixed server roles.
IS_SRVROLEMEMBER Security function that returns an integer indicating whether the current user login is a member of the specified server role.
 
 

Fixed Database Roles

sp_addrolemember
 System stored procedure that adds a security account as a member of an existing SQL Server database role in the current database. Permissions default to members of the sysadmin server role and the db_owner database role to add members to fixed database roles or user-defined roles. Owners of user-defined roles can add members to the roles they own and members of the db_securityadmin database role can add users to any user-defined role.
sp_droprolemember
 System stored procedure that removes a security account from a SQL Server role in the current database. Permissions default to members of the sysadmin server role and the db_owner and db_securityadmin database and are not transferable and only a member of the sysadmin> fixed server role or the db_owner fixed database role can remove users from a fixed database role.
 
sp_dbfixedrolepermission
 System stored procedure that displays the permissions for each fixed database role.
sp_helpdbfixedrole
 System stored procedure that returns a list of the fixed database roles.
sp_helprole
 System stored procedure that returns information about the roles in the current database.
IS_MEMBER Security function that returns an integer indicating whether the current user is a member of the specified NT group or SQL Server role.
 
 

Managing User-Defined SQL Server Roles

SQL Server gives administrators the ability to create their own roles so they can batch logins and define object permissions according to their project needs. Often, when you inherit a SQL Server installation and project, you will find a mixture of individual SQL Server logins and roles. Managing this mixture can often become a daunting task. SQL Server provides you with a group of system stored procedures and functions that can help you with this task.

sp_addrole
 System stored procedure that creates a new SQL Server role in the current database. Permissions default to members of the sysadmin server role, and the db_securityadmin and db_owner database roles and are not transferable.
sp_droprole
 System stored procedure that removes a SQL Server role from the current database. Permissions default to members of the sysadmin server role, the db_owner and db_securityadmin database roles, or the owner of the role and are not transferable.
sp_addrolemember
 System stored procedure that adds a security account as a member of an existing SQL Server database role in the current database. Permissions default to members of the sysadmin server role and the db_owner database role to add members to fixed database roles or user-defined roles. Owners of user-defined roles can add members to the roles they own and members of the db_securityadmin database role can add users to any user-defined role.
sp_droprolemember
 System stored procedure that removes a security account from a SQL Server role in the current database. Permissions default to members of the sysadmin server role and the db_owner and db_securityadmin database and are not transferable and only a member of the sysadmin fixed server role or the db_owner fixed database role can remove users from a fixed database role.
sp_helprole
 System stored procedure that returns information about the roles in the current database.
 
sp_helpuser
 System stored procedure that reports information about SQL Server users, Windows NT users, and database roles in the current database.
IS_MEMBER Security function that returns an integer indicating whether the current user is a member of the specified NT group or SQL Server role.