Microsoft this week unveiled its newest version of SQL server code named DENALI most probably will be known as SQL server 2011. I have had a bit of play around with the new version and found some of the new features being added to the Database engine. The first coolest thing that I looked at was NEW server roles. Starting from Denali you can create user-defined server roles and add server-level permissions to the user-defined server roles.
Creating them would be quite simple both with SSMS GUI (has got a new look in Denali) and Transact SQL. For each of the new server roles you can specify the access to server-scoped securable like Endpoints, Logins, servers, availability groups and any other user-defined server roles. Now with SSMS the fixed server roles (8 in total excluding public) are shown by a red icon and the user-defined sever roles and Public will be differentiated from them.
This Option gives more flexibility in terms of managing security for DBAs and other thing to be noted with this new server role is, the user-defined server role can be a member of all FIXED server roles except the sysadmin. This will really prove to be an important feature for managing SQL servers security area.
Sample TSQL to create your own Server role:
USE [master]
GO
— Creating User-defined server role LEKS
CREATE SERVER ROLE [Leks] AUTHORIZATION [sa]
GO
use [master]
GO
— granting administer bulk operations to Leks on local SQL server
GRANT ADMINISTER BULK OPERATIONS TO [Leks]
GO
use [master]
GO
— Granting impersonate ability for Leks to impersonate the SQL server login TEST
GRANT IMPERSONATE ON LOGIN::[TEST] TO [Leks]
GO
use [master]
GO
— Granting view definition for Leks on TEST login
GRANT VIEW DEFINITION ON LOGIN::[TEST] TO [Leks]
GO
use [master]
GO
–Granting view definition for Leks on TEST login
GRANT VIEW DEFINITION ON LOGIN::[sa] TO [Leks]
GO
use [master]
GO
— Granting connect on ENDPOINT to LEKS
GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [Leks]
GO
USE [master]
GO
— Creating another server role
CREATE SERVER ROLE [SQLA] AUTHORIZATION [sa]
GO
use [master]
GO
— Granting control on SERver role LEKS to server role SQLA
GRANT CONTROL ON SERVER ROLE::[Leks] TO [SQLA]
GO
— I have covered all area in granting permission except for Availability groups for which I am not running my SQL server on a cluster.
What are you still waiting for, go download CTP1 of SQL Denali from here http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en and start playing.
Leave a Reply