Search
Subscribe
Categories
- Backup and Restore
- Capacity Planning
- Compression
- Configure
- Contact me
- CPU
- Data Architecture
- Data Import and Export
- Database Mirroring
- DBA
- DBA Scripts
- DBCC
- Deadlocks
- Default Trace
- Development
- Disaster Recovery
- Disk Storage
- DMV
- Documentation
- Event Log
- Execution Plan
- Function
- High Availability
- Indexes
- Install & Setup
- Interviews
- IO Subsystem
- Isolation levels
- Jobs
- Latches
- Links
- Locking
- Memory
- Monitor Performance
- MS DTC
- MSDB
- Network
- Object Management
- Pagination
- Partitioning
- Performance
- Powershell
- Security Management
- SQL Agent
- SQL Azure
- SQL Blocking
- SQL Cachestore
- SQL DAC
- SQL Default Trace
- SQL DTS
- SQL Error Logs
- SQL Execution Plan
- SQL Optimizer
- SQL problems
- SQL Query Tuning
- SQL Server Error Tips
- SQL Server Management Studio
- SQL Server Profiler
- SQL Server Tuning
- SQL Trigger
- SQL View
- SQLServer-DBA News
- SSIS
- Statistics
- Stored Procedure
- T-SQL
- TempDB
- Tools
- TPC-H
- Transaction Logs
- View
- Virtualization
- Wait Stats
SQLServer-DBA.com Links
Recent Posts
- Shredding XML with Powershell and SQL Server
- Task Manager not showing correct SQL Server memory usage
- The value of training and How to convince your boss
- SQL Error 1204 - cannot obtain a LOCK resource at this time
- Multi-statement table valued function and inefficient Excecution Plan
- SQL Server Export to Excel with Powershell
- Who dropped the table in SQL Server
- List sql database roles
- SQL Execution Plan - Comparing Estimated Rows and Actual Rows
- DCOM got error "Logon failure: unknown user name or bad password” Event 1004
09 July,2010 by Jack Vamvas
During a security audit on SQL server 2005 server, I discovered an administrator had assigned the permission "sysadmin" to a logon.
He explained to me the reason : when they were on SQL Server 2000 it was required to allow a logon read\execute rights on SQL Agent Jobs.
We've now changed this policy to use the SQL Server Agent fixed roles - which allows a more detailed role assignement to logons.
It's worth noting these are mdsb database fixed roles
The choices are:
1) SQLAgentUserRole
2) SQLAgentReaderRole (includes SQLAgentUserRole)
3) SQLAgentOperatorRole (includes SQLAgentUserRole and SQLAgentReaderRole)
We've implemented by setting up a separate AD group , adding relevant Windows users - and then adding the logon to the server. That way we can maintain a tighter control on who can view\execute SQL Server Agent Jobs
Read More
How to create a SQL Server Security Audit - SQL Server DBA
Powershell sql server security audit
Author: Jack Vamvas (http://www.sqlserver-dba.com)
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |