{"id":200,"date":"2012-07-10T20:02:49","date_gmt":"2012-07-10T20:02:49","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=200"},"modified":"2014-03-05T20:15:36","modified_gmt":"2014-03-05T20:15:36","slug":"sql-server-user-defined-server-roles","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/sql-server-user-defined-server-roles\/","title":{"rendered":"SQL Server User Defined Server Roles"},"content":{"rendered":"

A new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level\/scope permissions to these roles. DBA’s have always had the ability to create user defined database roles which act as a security layer at the database level, but we’ve never been able to create roles at the server level until SQL Server 2012.<\/p>\n

In this post I will show you how to create user defined server roles using T-SQL and SQL Server Management Studio.<\/p>\n

What Permissions Can Be Assigned<\/h3>\n

First, to view the list of permissions that can be assigned to a user defined server role run the following query:<\/p>\n

\n
USE master \r\nGO\r\nSELECT * FROM sys.fn_builtin_permissions(DEFAULT) \r\nWHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') \r\nORDER BY class_desc, permission_name\r\nGO<\/pre>\n<\/div>\n
Create a Server Role in T-SQL<\/h3>\n
To create a server role called “juniordba” use the following:<\/p>\n
\n
USE master\r\nGO\r\nCREATE SERVER ROLE juniordba<\/pre>\n<\/div>\n
Next we will create a login called Brady and then add it to the new juniordba role that was created:<\/p>\n
\n
USE master \r\nGO\r\nALTER SERVER ROLE juniordba ADD MEMBER Brady<\/pre>\n<\/div>\n
We haven’t added any permissions to the server role, so Brady shouldn’t have access. To test this we can login as Brady and run the following query:<\/p>\n
\n
SELECT * FROM sys.dm_exec_connections<\/pre>\n<\/div>\n
As you can see we get the following error message:<\/p>\n
\n
Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.<\/pre>\n<\/div>\n
Click here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
A new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level\/scope permissions to these roles. DBA’s have always had the ability to create user defined database roles which act as a security layer at the database level, but we’ve never been able to create roles at […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[98,45,39,40],"class_list":["post-200","post","type-post","status-publish","format-standard","hentry","category-security","tag-security","tag-server-roles","tag-ssms","tag-tsql"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/200","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=200"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/200\/revisions"}],"predecessor-version":[{"id":201,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/200\/revisions\/201"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=200"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}