New Administration features in SQL server 2012
SQL Server 2012 SQL Server allows roles to be created at
the server level.
This can be done through the CREATE/ALTER/DROP SERVER
ROLE commands,
or through the GUI with a right-click on the Server Roles node, new menu item has
appeared below refer screenshot.
This brings up a new dialog box below refers screenshot.
The temporary DBA's login is added to the new server role
to give them the restricted set of sysadmin permissions, using below code:
USE master
GO
CREATE SERVER ROLE TEMPDBAROLE AUTHORIZATION sa
GRANT VIEW SERVER STATE TO TEMPDBAROLE
GRANT VIEW ANY DATABASE TO TEMPDBAROLE
GRANT VIEW ANY DEFINITION TO TEMPDBAROLE
DENY ALTER TRACE TO TEMPDBAROLE
ALTER SERVER ROLE TEMPDBAROLE ADD MEMBER temp_dbas
No comments:
Post a Comment