SQL Server：无需db_securityadmin角色，数据库所有者如何为其他登录授权？

Absolutely! You don't need to assign the securityadmin server role to your students to let them grant access to their own databases. Since each student will be the dbo (database owner) of the database they create, they already have full control over permissions within their own database—no server-level security roles required.

Here's a step-by-step breakdown tailored to your classroom setup:

1. First, Set Up Student Logins (Your Job as Admin) #

As the sysadmin, create a server-level login for each student upfront. For example:

CREATE LOGIN [StudentAlice] WITH PASSWORD = 'StrongClassPassword123!';
CREATE LOGIN [StudentBob] WITH PASSWORD = 'SecureClassPass456!';
-- Repeat this for all your students

This ensures all student logins exist on the server, so your students only need to know their classmates' login names to grant access later.

2. Students Create Their Databases (No Extra Roles Needed) #

Each student with the db_creator server role can create their own database, and they automatically become the dbo of that database:

CREATE DATABASE [AliceScienceProject];

As dbo, the student has full authority over everything inside their database—including creating users and assigning permissions.

3. Students Grant Access to Classmates (Their Job) #

When a student (like Alice) wants to give a classmate (like Bob) access to their database, they just need to run these commands inside their own database (make sure they switch to their database first):

-- 1. Map the classmate's server login to a user in your database
CREATE USER [StudentBob] FOR LOGIN [StudentBob];

-- 2. Grant the desired permissions (pick one or mix as needed)
-- Option 1: Read-only access to the entire database
ALTER ROLE db_datareader ADD MEMBER [StudentBob];

-- Option 2: Full read/write access
ALTER ROLE db_datareader ADD MEMBER [StudentBob];
ALTER ROLE db_datawriter ADD MEMBER [StudentBob];

-- Option 3: Granular access (e.g., only SELECT on a specific table)
GRANT SELECT ON [dbo].[LabResults] TO [StudentBob];

Crucially: The student doesn't need to "see" their classmate's login at the server level (which is what securityadmin allows). They just need to know the classmate's login name to create the corresponding user in their own database.

Why This Works #

The securityadmin role is for managing server-wide security tasks (like creating logins or changing server-level permissions). But database-level user creation and permission management are fully within the control of the database owner (dbo), even without any server-level security roles beyond db_creator.

Bonus Classroom Tips #

• Create a simple script template for your students to copy-paste, so they don't have to memorize exact syntax:

  -- Replace [ClassmateLogin] with your classmate's login name
  CREATE USER [ClassmateLogin] FOR LOGIN [ClassmateLogin];
  -- Choose one permission set below
  -- Option 1: Read-only access
  ALTER ROLE db_datareader ADD MEMBER [ClassmateLogin];
  -- Option 2: Read/write access
  -- ALTER ROLE db_datareader ADD MEMBER [ClassmateLogin];
  -- ALTER ROLE db_datawriter ADD MEMBER [ClassmateLogin];
  

• Remind students they only have control over their own databases—they can't modify permissions in other students' databases unless explicitly granted access there.

内容的提问来源于stack exchange，提问作者Benoit Desrosiers