Transcript

00:07

SQL Server Permissions with SQL Secure

Welcome to IDERA virtual Education for SQL Secure. Today we’re going to look at how to use SQL Secure to both analyze permissions as well as build reports on those permissions.

Gathering Data for Permissions

Before you can analyze permissions, it’s necessary that you run a snapshot to gather all of the objects on a single instance, including databases, views, Store, procedures, functions, as well as the permissions around those objects. To gather that data, we take a snapshot. That snapshot can be initiated manually by going up to the top, to the Server actions and selecting a snapshot. Or we can also schedule when the snapshot takes place by right clicking on the instance, select either Take Snapshot or go to the Properties to schedule the snapshot. The scheduling can be configured for daily, weekly, or monthly scheduling, even certain times of the day or certain days of the week. At this point, I have already executed my snapshot, and now I want to analyze permissions.

01:12

Explore Permissions

To do that, I can go to the Explore Permissions tab on the left hand side, identify the snapshot. Below the instance that I want to analyze. Specifically on the right hand side, I can see a list of all of the different accounts that were obtained as part of that snapshot process. By the way, that data is stored in a centrally located repository, so you don’t have to fear any performance impact by going out and running reports against this data. To analyze permissions, we can do a few things here. We can go to the User permissions and plug in a Windows User or a Group or SQL authentication account. Yes, this does also consist of Active Directory users and groups. We can also drill into a particular role and analyze the permissions around that role. Or we can also take the Management Studio style approach and select specific databases, or even expand on the databases to look at particular objects and then click on any object that we need to focus on.

02:18

Analyze Permissions

More specifically, to analyze the permissions around that particular object. This information can also be exported out to Excel if you need to share this information with others. As you can see, for this particular database, it’s displaying the grantee of different groups and users that have been assigned permissions and what level of permission they have on this particular table. Taking a step back to the user permissions, I can also plug into a particular group if I want to. To analyze that group, I can also assign or display the permissions for that group on a particular database as well. I will in my case, plug in one of my databases and by selecting the Show permissions, it will bring up the analysis of where assigned and effective permissions would be around this particular group. For assigned permissions, I can see that there are 187 items that have permissions.

03:20

SQL Server Objects Permissions

Expanding any of those object types like columns, tables, views, store procedures will display the individual objects permission, level the grantee, how they’re getting that access, as well as who granted that access. Again, all of this can be exported out. You can also select particular users as part of the group. I can expand the group view, group members, select an individual account and it will drill in to display the permissions for just that individual user. It will also, as you can see, note what roles and groups that user belongs to. We can go down into the assigned permissions and see the level of access that they provided, who it was that provided that access, what level of access they’ve been given, and to which objects, which of course can be exported out as well. Last around all of this data, we have a variety of different reports.

04:17

SQL Server Reporting

These reports can be pushed out to reporting services. That is not a requirement. However, you can run the reports right through the console. If you wanted to bring up all of your user permissions, there is a report for that, as well as individual user permissions. I’m selecting all user permissions, I can select which instances to build the permission report against, or select an individual instance, select View Report and this will bring up a nice polished report showing me all of my user permissions on this particular instance. Now, once this report is displayed, I can either go down the report and analyze it closer, or if I want a hard copy that I can cross off the list different areas that I have secured around different user access. Of course, I can export to Excel or print out from the console, so I have that hard copy to write notes on.

05:13

So that concludes our virtual education. Feel free to go out to our website, www.idera.com, or to our community site, Community, which is Community idira.com. We have a lot of good resources out there and also on our website you can download SQL Secure and give it a test for yourself. Thank you and have a great day.