jump to navigation

User defined server roles in SQL Server 2012 July 7, 2012

Posted by fofo in Sql Server, SQL Server 2012.
Tags:
trackback

In this post I would like to talk about a new security enhancement that was introduced in SQL Server 2012,user defined server roles.As we all know we had (and still have in SQL Server 2012) user defined database roles for database level permissions.In the latest version of SQL Server we can have custom – user defined server roles for server level permissions.Basically for a server-level principal we can grant server-level permissions with a mix of GRANT and DENY permissions.

Now we are ready to go on with our hands-on example.I will be using the SQL Server 2012 Express Edition

If you have an instance of SQL Server 2012 running in your machine,simply connect to it.

In a new query window type


use master;
GO

CREATE SERVER ROLE MyCustomServerRole;

GRANT CONTROL SERVER TO MyCustomServerRole;

DENY ALTER ANY LOGIN TO MyCustomServerRole;
DENY ALTER ANY SERVER AUDIT TO MyCustomServerRole;
DENY ALTER ANY SERVER ROLE TO MyCustomServerRole;
DENY CREATE SERVER ROLE TO MyCustomServerRole;
DENY UNSAFE ASSEMBLY TO MyCustomServerRole;

CREATE LOGIN Mary WITH PASSWORD = '!@#$%^&';

EXECUTE AS LOGIN ='Mary';

CREATE LOGIN Jane WITH PASSWORD = '!@#$%^&abc';

SELECT * FROM sys.dm_exec_cached_plans;

CREATE SERVER ROLE JaneRole;

REVERT;

ALTER SERVER ROLE MyCustomServerRole ADD MEMBER Mary;

EXECUTE AS LOGIN ='Mary';

CREATE LOGIN Jane WITH PASSWORD = '!@#$%^&abc';

SELECT * FROM sys.dm_exec_cached_plans;

CREATE SERVER ROLE JaneRole;

Let me explain what I am doing with the T-SQL code above.

  • In lines 4-6, I create a custom server role (MyCustomServerRole) and then grant it sysadmin permissions.
  • In lines 8-12, I take some of these permissions away.
  • In line 14 I create a SQL Server login,Mary.
  • In line 15 I impersonate the login Mary and I execute lines 18-22 as Mary.
  • All the statements in lines 18-22 fail since Mary has no server level permissions. That was expected.
  • Then in line 24 I revert to being a sysadmin
  • In line 26 I add Mary to the MyCustomServerRole and login again as Mary
  • The statement in line 30 will fail (there is no such permission for that login) – Have a look at the DENY permission statements above
  • The statement in line 32 will succeed
  • The statement in line 34 will fail (there is no such permission for that login) – Have a look at the DENY permission statements above

Now le’ts see how to create a windows login. We need to create a windows user first. Right-click on Computer icon on the desktop and then select Manage, expand Local Users and Groups and then create New User.I create a windows user named “nikolaosk”. Put any password you want. Have a look at the picture below.


I am creating a new login from that windows user. Then I add the login to the user defined server role.


use master;
GO
CREATE LOGIN [FOFO-PC\nikolaosk] FROM WINDOWS;

ALTER SERVER ROLE MyCustomServerRole ADD MEMBER [FOFO-PC\nikolaosk];

Now I will launch another instance of SSMS and log in as “nikolaosk”

In a command prompt type runas  /user:nikolaosk “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

Then type the password.

In the new SSMS application (running as nikolaos), in a new query  window type again


use master;
GO
CREATE LOGIN Jane WITH PASSWORD = '!@#$%^&abc';

SELECT * FROM sys.dm_exec_cached_plans;

CREATE SERVER ROLE JaneRole;

SELECT * FROM sys.fn_my_permissions(NULL,'server')

The first statement will fail since there are no permissions for that operation for the MyCustomServerRole user defined role that login nikolaosk is member of.

The second statement will succeed but the third statement will fail since there are no permissions for that operation for the MyCustomServerRole user defined role that login nikolaosk is member of.If you want to see what kind of server level permissions login nikolaosk has, execute the last statement.

Close the second SSMS application. Now go back to the first SSMS application.

In a new query window type and execute the following statements.


use master;
GO

DROP LOGIN Mary;
DROP LOGIN [FOFO-PC\nikolaosk];

Disconnect from the server instance and then close SSMS.

Hope it helps!!!

Comments»

1. Dot Net Rules : User defined server roles in SQL Server 2012 - July 7, 2012

[…] principal we can grant server-level permissions with a mix of GRANT and DENY permissions. (read more) Share Posted: Σάββατο, 7 Ιουλίου 2012 2:38 μμ από το μέλος […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: