376 Part I EXAM PREPARATION NOTE Where Have
376 Part I EXAM PREPARATION NOTE Where Have the Groups Gone? If you came up through an older version of SQL Server, such as SQL Server 7.0 or earlier, you re probably wondering where the groups are. Groups are now called roles. If you use one of the old-style group commands, such as sp_changegroup, you ll actually be changing the user s role. Roles have a lot more functionality than groups, such as enabling a user to belong to several roles. User Roles To provide the capability to grant multiple users access to the same objects the same way, SQL Server provides a mechanism for creating collections of users, called roles. Fixed Roles SQL Server provides you with a set of roles you can use to assign different levels of permission to users. There are two types of fixed roles. Fixed server roles are server-wide permissions that can be used regardless of the database you are in. Then there are fixed database roles, which apply to only one database. There is very little you need to know about the fixed roles for the exam; that is all relegated to the SQL Server Administration exam. For more information about the fixed server roles, you can look in SQL Server blogs Online at the overview topic under Roles. Defining Your Own Roles You can also define your own role. To create a new role, use the sp_addrole system-stored procedure, like this: sp_addrole rolename The rolename is the name of the role, which of course has to meet all of the other restrictions for naming objects in SQL Server, except that roles cannot contain backslash characters. Backslash characters create an empty role with no permissions. To add users to a role, use the sp_addrolemember stored procedure: sp_addrolemember rolename , security_account The security account parameter is the name of the security account that should be added to the role. A security account could include a user, a Windows NT account that has a user associated with it, or a Windows NT group. If a group is specified, then all the members of the Windows NT group who have associated users in the current database are added to the role. To give the role access to other objects, use the GRANT statement, as described earlier, and use the name of the role in place of the username.
For high quality java hosting services please check java web hosting website.