21
novembre
2010
[SQL Server 2011] Server roles
novembre
2010
Un article de Ptit_Dje
Creating custom server roles is a new feature coming with the SQL Server 2011 CTP1.
It allows to create custom roles on server level to define specific set of permissions on server objects.
Great ! What to do with this new feature ?
I want to share here 2 exemples I will definitively use:
Defining a Sharepoint Service account role which include both security administrator role and dbcreator role:
USE [master]
GO
CREATE SERVER ROLE [SharepointService]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [SharepointService]
ALTER SERVER ROLE [securityadmin] ADD MEMBER [SharepointService]
GO
GO
CREATE SERVER ROLE [SharepointService]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [SharepointService]
ALTER SERVER ROLE [securityadmin] ADD MEMBER [SharepointService]
GO
Defining a profiler user role:
CREATE SERVER ROLE ProfilerUsers
GO
GRANT ALTER TRACE to ProfilerUsers
GO
GRANT ALTER TRACE to ProfilerUsers
I’m pretty sure there’s a bunch of other roles which can be usefull, like a linked server manager role, and I’m glad to see this new feature available!