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