Tuesday, June 20, 2017

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.

 

Tuesday, May 30, 2017

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