If you want to set up a user in SQL Server 2005 or above so they can:
View all object definitions in all daabases (including SP source, etc)
View all data in all tables
View all Server level configuration
View SQL jobs
View Windows event logs, Perfmon, etc
Change nothing in SQL Server
Try the following...
a) Set up a Windows group and make it a SQL login. Add required users to the group.
b) Grant the group the following SQL rights:
- 'View Server State', 'View All Definitions', 'View All Databases'
- Grant DB_Datareader in all databases (granting this in Model will automatically add this to any databases created after this point)
- Add to SQLAgentReaderRole in MSDB
c) Grant the group the following Windows rights
- Add to local 'Remote Desktop Users' and 'Power Users' groups.
This does not give you a named Server Role, as it is impossible to create a new Server Role, but it does give the rights listed. These rights are often all a DBA needs to day for day work with SQL Server, they can have a different login with Sysadmin for when they want to change things.
PS To grant the 'View Server State' etc rights, open SSMS, r-c on the Instance and select Properties. Go to the Permissions tab and find what you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara