User Permissions in all databases in a SQL Server

Audit is a regular activity for databases. Sometime internal, sometimes external, sometimes bi-yearly, sometimes yearly, sometimes for preparation for external audit, sometimes corrections to existing audits and so on. Doing it manually is quite time-consuming and ofcourse, boring due to repetitive nature of activities. I prefer to automate generation of portions of audit information as much as possible. For database audits, mostly it is about logins, what access they have, permissions by groups, etc. So I thought of creating a table with columns that would contain the data/information that auditors generally seek and a simple SP that would populate this table. The SP would traverse user databases to get the information. Usually DBA's have a database specific to high end DBA activities like Service broker, job progression, etc. Let us call it my DBADB for this article's sake. This kind of table can reside in such a database where interference is minimal. So let us start with the table, dbo.DB_Permissions_By_Group some column names of which are self explanatory:

USE [DBADB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF;
go
CREATE TABLE [dbo].[DB_Permissions_By_Group](
 [DatabaseName] [varchar](32) NOT NULL,
 [Class_Desc] [varchar](128) NULL,
 [ObjectName] [varchar](64) NULL,
 [Minor_Id] [smallint] NULL,
 [Type] [varchar](24) NULL,
 [Permission_Name] [varchar](24) NULL,
 [State_Desc] [varchar](16) NULL,
 [GroupName] [varchar](64) NULL
) ON [PRIMARY]
with (data_compression = page)
GO

The other columns that need some description are following:
[Class_Desc] is type of objects which can be a database, schema, table, SP, etc.
[Type] is type of permission. For e.g. Execute, Select, Update, View, Showplan, etc.
[GroupName] is a Windows AD group that has some permission in some application database on this server. There can be one or more individual logins or groups within a given Windows group. And these members will have the same permission as the parent Windows group that they belong to.

Now to the SP that would populate this table, [dbo].[DB_Permissions_By_Group]. The SP is called 
[dbo].[dbp_Show_Permissions]. This SP scans all the non-system databases and fetches the permission on a given object for each user that exists in that database. Here is the code of this SP:

USE [DBADB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create or alter procedure [dbo].[dbp_Show_Permissions]
as
set nocount on
declare @l_exec_String varchar(8000)
       ,@l_db_name     varchar(32)
truncate table SBMDB.dbo.DB_Permissions_By_Group
declare DB_Cursor scroll cursor
for
select name from sys.databases
where name not in ('master', 'model', 'msdb', 'tempdb', 'SSISDB')
open DB_Cursor
fetch next from DB_Cursor into
     @l_db_name
while (@@FETCH_STATUS = 0)
begin
 select @l_exec_String = 'Use ' + @l_db_name + '; select db_name() DatabaseName, a.Class_Desc, case when a.class_desc = ' + '''' + 'Schema' + ''''
        + ' then  schema_name(a.major_id) else object_name(a.major_id) end ObjectName '
        + ',a.minor_id, a.[Type], a.[permission_name], a.state_desc ,b.name from sys.database_permissions a '
        + 'inner join sys.database_Principals b '
        + 'on a.grantee_principal_id = b.principal_id '
        + 'where b.name like ' + '''%MyDomain\%'''
 insert into [DBADB].dbo.DB_Permissions_By_Group (DatabaseName, Class_Desc, ObjectName, Minor_Id, [Type], [Permission_Name], State_Desc, GroupName)
 exec (@l_exec_String)
    fetch next from DB_Cursor into
            @l_db_name
end
close DB_Cursor
deallocate DB_Cursor
set nocount off;
GO
Explanation: Basically, it joins the DMV's sys.database_permissions (for permissions) and sys.database_principals (for users) and matches them based on the principal_id which is the ID of the database user for a given login. It does it for each user database and puts the result in our table,  [DBADB].dbo.DB_Permissions_By_Group.

Since it sifts through only the DMV's in each user database, performance is not a concern. It takes less than a few seconds to go through a few dozen databases. Once this SP gets executed, the user needs to simply extract the contents of  the table, [DBADB].dbo.DB_Permissions_By_Group into an Excel file and send it to auditors.


Execution: execute [DBADB].[dbo].[dbp_Show_Permissions]

Results:      Select * from [DBADB].dbo.DB_Permissions_By_Group with (nolock)

 

Finding Permission for user tables by Login

Sometimes, we need to find permissions of a SQL Server user or a Windows login on database tables. Doing so using the Object Explorer > Security > Logins or Object Explorer > [Database] > Security > Users in SSMS is quite tedious and hard to document. One simple way could be the use of the T-SQL system function, HAS_PERMS_BY_NAME. Let us say, I want to find out the permissions that a login called ReportLogin used in the data sources of all SSRS/Sharepoint reports has on the application tables  in a database called MyDB. This is what I do:

Use MyDB
go
execute as login = 'ReportLogin'
go
select SUSER_sNAME()
go
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');
SELECT DB_NAme() DatabaseName
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'SELECT') AS have_SELECT
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'INSERT') AS have_INSERT
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'UPDATE') AS have_UPDATE
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'DELETE') AS have_DELETE
      ,QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)  TABLE_NAME
      ,*
   FROM sys.objects where [type] in ('U')
go
revert
go
select SUSER_sNAME()
go

 

That does the following:

1. Executes the code as the SQL login called ReportLogin,
2. Checks if that login has any access to the database MyDB,
3. Gives the result as to the type of access (select, insert, update, delete) in the first four columns for each user table and
4. Then reverts back to my login.

 If it is a windows login whose access needs to be verified, simply substitute it as

Execute as Login = 'Domain\LoginName'

The rest of the code remains the same. Please note that this code doesn't work for Windows groups. That is because 'Execute As Login' doesn't work for Windows Groups. The next article would address this goal.

Possibilities:

1. The code above can be converted to an SP.
2. The SP can loop through differ logins in the syslogins catalog view.
3. For each login, it can insert data into a table.
4. Adding exec sp_MSforEachDB to execute that SP would execute it on all databases if DB_NAME() is added in the select list.
5. I would leave that portion to the reader.

 

Find occurrence of a specific day in a month

I am resuming blogging. Sorry for being away for so long. Many a time, we encounter situations where something is to be executed on a 2nd Saturday, a 4th Tuesday of a month or likewise. It would be so handy to have something that easily verifies the occurrence of a specific weekday in a given month and then executes other TSQL code after such an occurrence has been verified. A reusable code that returns a true or false is much easier than actually going through the dates and counting the occurrence of a specific day. So, I embark on creating a scalar function for this purpose (In this function, I have used CTE, Recursive CTE, IIF, EOMonth):

USE [DB_NAME]
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
  
create function [dbo].[dbf_Is_day_Nth_occurrence]
(@i_Day varchar(10)
,@i_Nth_Occurrence tinyint)

Returns bit
as
/************************************************************************************************************************************************************
 Date      Altered by          Description
__________ ___________________ ___________________________________________________________________________________________________________________________
04/15/2017 Suresh K. Maganti   Created. True or False. is it the 2nd Tuesday of this Month?
************************************************************************************************************************************************************/
begin
   declare @o_Return bit

;with CTE_Base (First_of_Month, Last_of_Month)
as
  (select convert(datetime, convert(varchar(4), year(getdate())) + right('0' + convert(varchar(2), month (getdate())), 2) + '01'), convert(datetime, EOMonth(getdate())) )
--select * from CTE_Base

,CTE_Recursive (The_Date, The_Day, Occurrence_in_Month, Last_of_Month)
as
  (select First_of_Month, datename(WEEKDAY, First_of_Month), 1, Last_of_Month from CTE_Base
   union all
   select The_Date + 1, datename(WEEKDAY, The_Date + 1), Occurrence_in_Month + 1, Last_of_Month from CTE_Recursive
   where The_Date < Last_of_Month)

,CTE_Final (The_Date, The_Day, Nth_Occurrence_of_Day)
as
  (select The_Date, The_Day, Row_Number() over(partition by The_Day order by Occurrence_In_Month)
   from CTE_Recursive)

  select @o_Return = iif ( (The_Day = @i_Day) and (Nth_Occurrence_of_Day = @i_Nth_Occurrence), 1, 0)
  from CTE_Final
  where convert(varchar, The_Date, 112) = convert(varchar, getdate(), 112)

  return @o_Return
end
go


Today, 05/30/2017 is 5th Tuesday of May 2017. Now, let me check using the function if today is the 1st Sunday of this month:

select [dbo].[dbf_Is_day_Nth_occurrence] ('Sunday', 1)
I get a 0. The answer is No.

Let me try to see if it is the 5th Tuesday of this month:

select [dbo].[dbf_Is_day_Nth_occurrence] ('Tuesday', 5)
I get a 1. The answer is Yes.

So, I can do the next set of things that I can do for today as follows:

if [dbo].[dbf_Is_day_Nth_occurrence] ('Tuesday', 5) = 1
begin
   Print 'The next article would be a bigger one.'
end
go
 

Index Keys & Properties as a step in Database Tuning

Introduction


As DBA's, we encounter long running queries that end-users compalin about. Indexing strategy is one area of database where developers usually tend to create indexes on the fly without much consideration for or analysis on how an index that helps one query marginally can slow down a bunch of other queries or stored procedures drastically. Some (not  all) of the situations that we usually come across are following:

• Dbo.Orders table is being joined to dbo.Order_Details table but both of them exist on the same filegroup and on the same set of underlying discs. This would cause disc queuing and the consequent slowness. 
• Dbo.Orders table is being joined to dbo.Order_Details table on the common column Product_ID but Product_ID is the leftmost colmn of a non-clustered index on dbo.Orders but is the third column in another non-clustered index on dbo.Order_Details. So these indexes don't help this join much. 
• Updates are taking place on a column that has a non-clustered index that is compressed. It would be good not to have an index at all on a column being updated. In case such an index is really required, not compressing it would be beneficial. Otherwise the update is much slower than when the index is uncompressed.
• A majority of values in a particular column used in the WHERE clause of a query have a value but the query is designed to fetch only such rows where the value is null. May be a filtered-index would have helped speed it up.  

In such situations and some others, we would like to have a quick gance at atleast the following to begin with:

• Whether the two tables being joined are physically resident on different filegroups.
• The columns being joined are the leftmost columns in their respective indexes.
• The column being updated is not part of an index (first of all) that is compressed in any way (definitely not).
• In case, a query is looking for a small subset of values from a column, is the column a part of an index that has the appropriate filter defined on the index.
• Etc.

 Instead of trying to procure such relevant information painstakingly one at a time, an easier way would be to have a stored procedure return the same. This article is aimed at creating such a stored procedure that could come in handy in performance situations described above.

Details of the Stored procedure
The text of the store procedure is given below:

If exists (select 1 from sys.procedures              
               where USER_NAME(schema_id) = 'dbo'
               and name = 'dbp_Find_Index_Properties_by_Column'
               and type = 'P')
begin
     drop procedure dbo.dbp_Find_Index_Properties_by_Column
end
go
create procedure dbo.dbp_Find_Index_Properties_by_Column
  @i_Table_Name     varchar(64) = null
,@i_Column_Name varchar(24) = null
,@l_Column_Position_in_Index tinyint = null
as
*******************************************************************************************************************************
Created by: Suresh K Maganti
Purpose    : Find if a column is used in an index. View additional properties of the index.
                   For now, I have included only clustered and non-clustered indexes.
Input
Parameters: If provided, the result set is appropriately filtered. Else, the complete result set is returned.
********************************************************************************************************************************/
set  nocount on;

with CTE_Base (Table_Name, Index_Name, Leftmost_Column_in_Index, Position_in_Index, Columns_in_the_Index, Index_Type, Is_Primary_Key, Is_The_Index_Unique, Fill_Factor_When_Created, File_Group_Name, Is_It_A_Filtered_Index, Filter_Definition, Is_An_Included_Column, Is_Compressed, Type_Of_Compression_Used)
as
(    select C.Name Table_Name, b.name Index_Name
,col_name(b.[object_id], (select e.column_id from sys.index_columns  e
                                          where e.index_column_id = 1
                                          and     e.[object_id] = b.[object_id]
                                          and     e.index_id = b.index_id))                   Leftmost_Column
,case
    when @i_Column_Name is null then null
    else a.key_ordinal
end                                                                                                         Position_in_Index

,replace((select col_name(f.[object_id], f.column_id) + ' ###' 'data()'
               from sys.index_columns f
               where f.[object_id] = a.[object_id]
               and     f.index_id = a.index_id
               order by f.key_ordinal asc
               for XML Path ('')), '###', ',')                                                   Columns_in_the_Index

,case b.Type
      when 0 then 'Heap'
      when 1 then 'Clustered'
      when 2 then 'Nonclustered'
      when 3 then 'XML'
      when 4 then 'Spatial'
end                                                                                                         Index_Type

      ,case b.is_primary_key
       when 1 then 'Yes'
       else 'No'
end                                                                                                         Is_Primary_Key


,case b.is_unique
       when  1 then 'Yes'
       else 'No'
end                                                                                                        Is_The_Index_Unique

,b.fill_factor                                                                                          Fill_Factor_When_Created

,FILEGROUP_NAME (b.data_space_id)                                            File_Group_Name

,case b.has_filter
     when 1 then 'Yes'
     else 'No'
end                                                                                                        Is_It_A_Filtered_Index
    
,b.filter_definition

,case a.is_included_column
      when 1 then 'Yes'
      else 'No'
end                                                                                                        Is_An_Included_Column

,case
     when d.data_compression > 0 then 'Yes'
     else 'No'
end                                                                                                       Is_Compressed

,d.data_compression_desc Type_Of_Compression_Used

from                   sys.tables c left join sys.index_columns a

on c.[object_id] = a.[object_id]

inner join sys.indexes b

on    a.[object_id] = b.[object_id]
and  a.index_id = b.index_id

inner join sys.partitions d
on     b.[object_id] = d.[object_id]
and   b.index_id = d.index_id

where b.[type] in (1, 2)
and a.key_ordinal = isnull(@l_Column_Position_in_Index, a.key_ordinal)
and OBJECTPROPERTY (b.[object_id], 'IsSystemTable') = 0
and OBJECTPROPERTY (b.[object_id], 'IsTable') = 1
and col_name(b.[object_id], a.column_id) = ISNULL(@i_Column_Name, col_name(b.[object_id], a.column_id))
and c.name = ISNULL(@i_Table_Name, c.name))


select distinct Table_Name, Index_Name, Leftmost_Column_in_Index, Position_in_Index
,substring(Columns_in_the_Index, 1, LEN(Columns_in_the_Index) - 1) Columns_in_the_Index
,Index_Type, Is_The_Index_Unique, Fill_Factor_When_Created, File_Group_Name, Is_It_A_Filtered_Index, Filter_Definition, Is_An_Included_Column, Is_Compressed, Type_Of_Compression_Used

from CTE_Base
order by Table_Name

set nocount off;

go

For now, I have included only clustered and non-clustered index including filtered indexes.
This stored procedure basically uses a few catalogue views:

• sys.partitions
• sys.tables
• sys.index_columns
• sys.indexes

and a few system functions like:

• OBJECTPROPERTY
• FILEGROUP_NAME
• COL_NAME

and a new construct FOR XML PATH that was introduced in Microsoft SQL Server 2008. XML PATH is being used in this stored procedure to generate a comma separated list of columns that are part of a given index ordered by their order of occurrence within the index. You may notice an interesting fact here. I am using ORDER BY inside the subquery. Normally ORDER BY is the last part of any SELECT statement. But when used with XML PATH, it comes before the FOR XML PATH construct. Another thing you may notice is that when ORDER BY is used inside a subquery, usage of TOP 100 PERCENT after the keyword SELECT, is mandatory in most cases. Not so in this case.

The rest are all basic joins.

Now we will get to the usage of this stored procedure.


Usage
For simplicity, I have used Microsoft's Adventureworks database in the examples below.

Execution 1:


Please execute the following TSQL code without any input parameters:

execute dbo.dbp_Find_Index_Properties_by_Column

                                                                                   Figure 1

Results for all clustered and non-clustered indexes in the Adventureworks database are displayed.



Execution 2:


Please execute the following TSQL code. It will display the results for all tables and their indexes in the Adventureworks database the column AddressID is a part of:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', null

                                                                                 Figure 2

Execution 3:

Now execute the following TSQL statements to change the fillfactor on the index Purchasing.VendorAddress.IX_VendorAddress_AddressID from 80 to 50:

use Adventureworks
go
alter index IX_VendorAddress_AddressID on Purchasing.VendorAddress rebuild with (statistics_norecompute = on, fillfactor = 50, Online = ON, maxdop = 8, sort_in_tempdb = on, allow_page_locks = on)
go

Now execute the statement below to see if the change in fill factor for this index has been captured:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', null

                                                                             Figure 3


As you have noticed in the screenshot above, the stored procedure displays 50 (Figure 3) intead of 80 (Figure 2) for the fill factor for this index.


Execution 4: 

 Now let us add a secondary filegroup named SecondaryFileGroup to the database Adventureworks, add a data file to this new filegroup, then drop the index Purchasing.VendorAddress.IX_VendorAddress_AddressID and finally recreate it on the new filegroup, SecondaryFileGroup using data compression and a filter on the AddressID column. The same is accomplished by executing the following code. Please note that you may have to change the path of the new data file in the code below as per you custom configured disc layout: 

alter database Adventureworks add filegroup SecondaryFileGroup
go
alter database Adventureworks add file (Name = Adventureworks_2, filename = 'E:\Data3\MSSQL\Data\Adventureworks_2.ndf', size = 10MB, Maxsize = 100MB, filegrowth = 10%) to filegroup SecondaryFileGroup
go
drop index Purchasing.VendorAddress.IX_VendorAddress_AddressID
go
create index IX_VendorAddress_AddressID on Purchasing.VendorAddress (AddressID)
where AddressID is not null with
(statistics_norecompute = on, fillfactor = 50, data_compression = Page, Online = ON, maxdop = 8, sort_in_tempdb = on, allow_page_locks = on) on SecondaryFileGroup
go
 Now execute the statement below to see if the changes for this index have been captured:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', null

                                                                                 Figure 4

The changes have been captured.

Execution 5:

Lets check to see on which tables the column, AddressID is being used in an index while occurring at the second position.

Please execute the statement below to find this out:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', 2

                                                                                           Figure 5

AddressID occurs as the second column in the three indexes on the three tables shown in Figure 5 above. 

Summary:
The stored procedure dbo.dbp_Find_Index_Properties_by_Column can be used to find various aspects of how a column is involved within an index. This SP also provides details on various indexes which can then be used to refine existing indexing strategy based on various application stored procedures. I haven't delved deeper into performance optimization with respect to indexing strategy in this article. I have only attempted to provide an easier starting piont for a preliminary analysis.

As usual, please let me know of your thoughts and comments.