Explicit DBO permissions on TempDB for a domain group

To do this we created a stored procedure and added it as a startup procedure so that whenever the SQL server service is restarted then the stored procedure will automatically grant the access to the group on the tempdb.

Add the domain group to the SQL server logins and then create the following procedure:

create proc sp_grant_access

as 
exec
tempdb..sp_grantdbaccess 'ggaurav03\tim','g22' 

 exec
tempdb..sp_addrolemember 'db_owner','g22' 

go 

sp_procoption 'sp_grant_access','startup', 'on'

 

Happy Learning & Troubleshooting!!

Disclaimer:  Everything here, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.