SQL Server Limiting Data Access for a Server Admin

If a user is granted the following server roles they still cannot access the data of databases they are not a user or dbo.


serveradmin - can change server configuration parameters and shut down the server.

setupadmin - can add or remove linked servers, manage replication, create, alter or delete extended stored procedures, and execute some system stored procedures, such as sp_serveroption.

securityadmin - can create and manage server logins and auditing, and read the error logs.

processadmin - can manage the processes running in SQL Server.

dbcreator - can create, alter, and resize databases.

diskadmin - can manage disk files.



----------- example script -----------

use master
go



EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'securityadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'serveradmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'setupadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'processadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'diskadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'dbcreator'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'bulkadmin'
GO

----------- example script -----------
To allow backing up of a database they do have access to, then can use the following:


use [user_database]
go

grant backup database to [testuser]
go