Join Martin Guidry for an in-depth discussion in this video Creating user-defined server roles, part of SQL Server 2012 New Features.
Microsoft SQL Server has long had the concept of server roles. So under Security, we can see the available server roles hasn't changed much in a few generations. We have things like the securityadmin, serveradmin, dbcreator, et cetera, et cetera. Now in SQL Server 2012 for the first time, we can create our own custom server roles. They do not yet have a graphical interface for this. So if you right-click, you don't see any options that say Create New Server Role.
We have to do all this in code. So I have staged some code for you in your exercise files. Copy and paste all of that over into Management Studio. The hypothetical I'm going through here is that I want to create a new server role called ReadOnly_Admin. This is because maybe I am troubleshooting something with an outside vendor or some other admins. And in order to troubleshoot properly, they need to be able to look at a lot of things on my server. But I don't want them changing anything on my server.
So I'm giving them a series of permissions that are basically Read Only. I'm calling that sever role ReadOnly_Admin. So I will execute this, come over here to Server Roles, hit Refresh, and we now see the ReadOnly_Admin. So now, I can go to the logins, and on any of the logins, I can add them to that server role. So I look at Properties > Server Roles, and my custom one ReadOnly_Admin is now available in that list.
So this is a fairly simple tool Microsoft has added that has a potential to significantly reduce administrative effort. There aren't any new permissions here. It's not like I can grant or deny access to anything that I couldn't previously. But it does mean by creating a custom server role that I could possibly reduce the administrative effort. If I have 5 or 10 people that all need the exact same access, rather than having to set them up all individually, I could create a new role, put all of them in that role, and administer them as a group which is going to be less effort in the long run.
- Choosing an edition
- Finding and downloading SQL Server 2012
- Using FileTables to enhance FileStream
- Searching unstructured data semantically
- Searching text
- Exploring the Watch, Quick Watch, and Quick Info windows
- Creating user-defined server roles
- Understanding the changes to replication
- Exploring new types of indexes and new options on indexes
- Accommodating changes to database structure