04 February, 2011

SQL 11–Denali Creating Server Roles


One of the neat new features of the new SQL Server, is the ability to create server roles, this was not possible before, you could only create a role on the database, and grant database level permissions to it. Now you can create a Server Role, and grant permissions to it, this allows us to group permissions and make then more visible, as its really boring to check every login for its server level permissions, so here’s how to use it.

The permissions you need for this are:

  • CREATE SERVER ROLE, which is present at sysadmin server role.
  • Impersonate if you set ownership for this role.

For a full doc check this link.

Just one note: a Server role cannot contain permissions for database level securables (objects).

I’m going to demonstrate how to create a server role, and add a user to it.
But first I’m going to create a SQL User for demonstration purposes.

CREATE LOGIN TEST
WITH PASSWORD = N'TEST'
,CHECK_EXPIRATION = OFF
,CHECK_POLICY = OFF
GO

Then we get to the feature display:

CREATE SERVER ROLE VIEW_STATE
GO
GRANT VIEW SERVER STATE TO VIEW_STATE
GO
ALTER SERVER ROLE VIEW_STATE
ADD MEMBER TEST
GO

Note that on the first statement we’re creating the role with the name of SERVER_STATE, then we’re adding the permission VIEW SERVER STATE to the role, and finally adding the user to the role, notice how we add the user to the role, its pretty different that the database role which uses the sp_addrolemember procedure.


Well I hope’s this helps you see some of the goodness that the new SQL 11 will bring us.


Greetings from the
Developer Seer

No comments:

Post a Comment