Transcript

Expand

Welcome to IDERA Virtual Education for SQL Secure. Today we are going to look at how to use SQL Secure to both analyze permissions as well as build reports on those permissions. Before you can analyze permissions, it is necessary that you run a snapshot to gather all of the objects on a SQL Server instance including databases, views, stored procedures, functions as well as the permissions around those objects. So 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. So at this point, I have already executed my snapshot. And now I want to analyze permissions. So 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 do not have to fear any performance impact by going out and running reports against this data. So 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. And yes, this does also consists 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 particular objects. And then click on any object that we need to focus on 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. And as you can see for this particular database, it is displaying the grantee of different groups and users that have been assigned permissions and what level of permission they have on this particular table. So taking a step back to the user permissions, I can also plug into a particular group. If I want to analyze that group, I can also assign or display the permissions for that group on a particular database as well. So 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. So for assigned permissions, I can see that there are a hundred and eighty-seven items that have permissions. Expanding any of those object types like columns, tables, views, stored procedures will display the individual objects permission level, the grantee, how they are getting that access, as well as who granted that access. And again all of this can be exported out. You can also select particular users as part of the group. So 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 that user belongs to. But then we can go down into the assign permissions and see the level of access that they are provided. Who it was that provided that access? What level of access they have been given down 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. These reports can be pushed out to reporting services. That is not a requirement, however. You can run the reports right through the console. So if you bring up all of your user permissions. There is a report for that as well as individual user permissions. So I am 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 a little bit 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 then of course I can export to Excel or print out from the console. So I have that hard copy to write notes on. So that concludes our Virtual Education. Feel free to go out to our website www.idera.com or to our community site community.idera.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.

How to Analyze Permissions and Build Reports in SQL Secure

IDERA SQL Secure is a security analysis solution that identifies SQL Server security violations and ensures security policies are enforced. SQL Secure allows DBAs to view the permission settings of their individual users, roles, and objects, at a particular point in time. It also enables DBAs to audit all users and object permissions on SQL Server instances that have been registered with SQL Secure.

In SQL Secure, DBAs can grant or deny permissions to a user, group, or role for a particular server or database object. The explore permissions view in SQL Secure enables DBAs to review the security information on three levels of Enterprise level, SQL Server level, and Individual user. Learn More →

Start a FREE Trial of SQL Secure
Share This
Contact IDERA: