How to Create a SQL Server 2008 Administrator Account with Full Privileges


1 views

SQL Server 2008 offers two authentication modes: Windows Authentication and SQL Server Authentication. For administrative purposes, you'll typically need to create a SQL Server account with sysadmin privileges.

Here's the simplest way to create an admin account using SQL Server Management Studio (SSMS):

  1. Connect to your SQL Server 2008 instance using SSMS
  2. Expand the "Security" folder in Object Explorer
  3. Right-click "Logins" and select "New Login"
  4. Enter the desired login name
  5. Select "SQL Server authentication" and provide a password
  6. Under "Server Roles", check the "sysadmin" checkbox
  7. Click "OK" to create the account

For script-based administration or automation purposes, you can use T-SQL commands:

USE [master]
GO
CREATE LOGIN [AdminUser] WITH PASSWORD=N'StrongPassword123!', 
DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'AdminUser', @rolename = N'sysadmin'
GO

After creation, verify the account has proper permissions:

SELECT name, type_desc, is_disabled 
FROM sys.server_principals 
WHERE IS_SRVROLEMEMBER('sysadmin',name) = 1
ORDER BY name;

When creating admin accounts:

  • Always use strong passwords (minimum 12 characters with complexity)
  • Limit the number of sysadmin accounts
  • Consider using Windows Authentication when possible
  • Document all administrative accounts

If you encounter problems:

-- Check if SQL Server is in mixed authentication mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Windows Auth Only]
-- 0 means mixed mode is enabled

To enable mixed mode if needed:

  1. Right-click the server in SSMS and select "Properties"
  2. Go to "Security" page
  3. Select "SQL Server and Windows Authentication mode"
  4. Restart the SQL Server service

SQL Server 2008 supports two authentication modes: Windows Authentication and SQL Server Authentication. For administrator access, you typically want to create a SQL login that has sysadmin privileges. This ensures the account has full control over the SQL Server instance and all databases.

The most straightforward method is to execute T-SQL commands through SQL Server Management Studio (SSMS):

USE [master]
GO
CREATE LOGIN [AdminUser] WITH PASSWORD=N'StrongPassword123!',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'AdminUser', @rolename = N'sysadmin'
GO

For those who prefer graphical interface:

  1. Connect to your SQL Server instance in SSMS
  2. Navigate to Security > Logins in Object Explorer
  3. Right-click and select "New Login"
  4. Enter login name and select SQL Server authentication
  5. Set password and uncheck "Enforce password policy" if needed
  6. Under Server Roles, check "sysadmin"
  7. Click OK to create the login

After creating the account, verify the sysadmin role assignment:

SELECT name, type_desc, is_disabled 
FROM sys.server_principals 
WHERE IS_SRVROLEMEMBER('sysadmin',name) = 1
ORDER BY name;

While creating admin accounts is necessary, consider these security measures:

  • Always use complex passwords (minimum 12 characters with mixed cases, numbers, and symbols)
  • Enable auditing for privileged accounts
  • Consider using Windows Authentication for centralized management
  • Regularly review and clean up unnecessary admin accounts

If you encounter problems:

-- Error: Login failed for user 'AdminUser'
-- Solution: Ensure SQL Server authentication is enabled in Server Properties
-- Run this to check:
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Windows Authentication]
-- 1 means only Windows Auth is enabled