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