SQLGeeksPro

Hi there! In this blog post, we will explore a SQL query that allows you to list all database users with Permissions in SQL Server. As a DBA you must be able to identify which users have access to specific databases and what permissions they possess.

Understanding Database Users and Permissions

Before we dive into the SQL query, first let’s clarify the concepts of database users and permissions.

Database Users:

A database user is an individual or application that interacts with a SQL Server database.
Each user account is mapped to a Windows login or a SQL Server login.
Users are associated with roles and are granted permissions to perform specific actions within the database.

Permissions:

In SQL Server, permissions are used to control access to database objects, such as tables and views. Each user in a database has a set of permissions that determine what they are able to do within the database, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE.

Query to List All Database Users With Database Role Permissions

USE TestDB  -- Change the db Name
GO
SELECT
       ServerName=@@servername, Dbname=db_name(db_id()),p. name as UserName, 
       p. type_desc as TypeOfLogin, pp. name as DatabaseRoleAssigned, pp. type_desc as TypeOfRole
       FROM sys.database_role_members roles
       JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
       JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
       WHERE p. type_desc NOT IN('DATABASE_ROLE','ASYMMETRIC_KEY_MAPPED_USER')
       AND p. name NOT IN ('sys','INFORMATION_SCHEMA','guest','dbo')

Output:

Generally speaking the above output is neat. Point often overlooked is we only got the details of the users which are part of any database roles. In other words I still need to know about users having Execute permission to any Stored Procedure, SELECT/INSERT/UPDATE permission on specific table etc.

Given these points let’s look into the below query.

Query To List All Database Users With Permissions:

USE TestDB  -- Change the db Name
GO
SELECT
   @@servername as SQLInstanceName,
   Dbname=db_name(db_id()),
   u.name as 'UserName',
   u.type_desc as LoginType,
   p.permission_name as 'Permission',
   o.name as 'ObjectName',
   o.type_desc as 'ObjectType'
FROM
sys.database_permissions p
INNER JOIN
sys.objects o ON p.major_id = o.object_id
INNER JOIN
sys.database_principals u ON p.grantee_principal_id = u.principal_id

Output:

As can be seen in the above example output the query shows the permissions on specific objects. Hence you need these two queries to List All Database Users With Permissions.

Important to realize the following points:

• The queries returns information about the database roles and permissions on database-level. Therefore it will not return information about server-level permissions.
• Both the queries returns information about SQL and Windows users but does not include permission of roles. Hence If you want to see the permissions of database roles please feel free to tweak the query.
• These will only shows the permissions for the specific connected database . Hence to check the permissions for all users in all databases, run the query against each database individually or modify the query. You can refer the link Retrieve all permissions for a user in all user databases which provides assigned database roles for a login in all databases.

Conclusion:

In summary managing permissions in SQL Server is a crucial aspect of database administration. Hence I have provided a simple introduction of users and permissions before diving into the query.
With this in mind, you can easily use the queries to list all database users and their permissions. This will certainly help you maintain a secure and compliant database environment.
Regularly reviewing and auditing permissions is essential to ensure data integrity and security in your SQL Server databases.

Introduction

A calendar table is useful for any SQL Server database, as it allows for easy querying and reporting of date-based data. Reporting often requires aggregating or returning data based on date attributes such as weekdays, holidays, quarters, or time of year. Creating a calendar table can save time, improve performance, and increase consistency in data. In this guide, we will walk through the process of creating a calendar table in SQL Server, including the necessary code and best practices for maintaining and using the table.

You can change some of these details to experiment on your own.

Why Use a Calendar Table?

There are several benefits to using a calendar table in your SQL queries. First and foremost, a calendar table can greatly simplify date-based queries. Without a calendar table, you may have to use complex logic to extract the year, month, day, etc. from a date field. With a calendar table, you can simply join to it and use the pre-calculated date fields.

Another benefit of a calendar table is that it can be used to quickly fill in missing dates in your data. If you have a sales table that only has entries for dates when there were actual sales, a calendar table can be used to fill in the missing dates and show “zero” sales for those dates. This can be particularly useful when creating graphs or charts that need to have a consistent X-axis.

Finally, a calendar table can be used to quickly apply filters to your data based on dates. For example, you can use a calendar table to easily filter your data to only show records from the current month, or the last 30 days.

Step 1: Create the Table Structure

The first step in creating a calendar table is to create the table structure. We will be using the following code to create the table. You can also include other columns such as quarter or fiscal year, depending on your specific requirements.

CREATE TABLE tblCalendar(
       [Date] Date NOT NULL,
       [Day] char(10) NOT NULL,
       [DayOfWeek] tinyint NOT NULL,
       [DayOfMonth] tinyint NOT NULL,
       [DayOfYear] smallint NOT NULL,
       [PreviousDay] date NOT NULL,
       [NextDay] date NOT NULL,
       [WeekOfYear] tinyint NOT NULL,
       [Month] char(10) NOT NULL,
       [MonthOfYear] tinyint NOT NULL,
       [QuarterOfYear] tinyint NOT NULL,
       [Year] int NOT NULL,
       [IsWeekend] bit NOT NULL,
    )
 
ALTER TABLE tblCalendar
ADD CONSTRAINT PK_CalendarDate PRIMARY KEY (Date); 

You should add a primary key to ensure the data is unique and can be easily searched.

Step 2: Populate the Table with Data

Once the table structure is created, it’s time to populate it with data. You can use a series of INSERT statements to add data for a specific range of dates. In the below example I am inserting 365 days of data starting from 1st Jan’23.

DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = '2023-01-01' --Put the start date as per your requirement
SET @EndDate = DATEADD(d, 365, @StartDate) --Change the no. of days as needed
WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO tblCalendar values
             (
                   @StartDate,
                              CONVERT(CHAR(10), DATENAME(WEEKDAY, @StartDate)),
                              CONVERT(Tinyint,DATEPART(WEEKDAY, @StartDate)),
                              CONVERT(Tinyint,DATEPART(DAY, @StartDate)),
                              CONVERT(smallint, DATEPART(DAYOFYEAR, @StartDate)),
                              DATEADD(day, -1, CONVERT(DATE, @StartDate)),
                              DATEADD(day, 1, CONVERT(DATE, @StartDate)),
                              CONVERT(tinyint, DATEPART(WEEK,@StartDate)),
                              CONVERT(CHAR(10), DATENAME(MONTH, @StartDate)),
                              CONVERT(TINYINT, DATEPART(MONTH, @StartDate)),
                              CONVERT(TINYINT, DATEPART(QUARTER,@StartDate)),
                              CONVERT(INT, DATEPART(YEAR,@StartDate)),
                              CASE 
                                   WHEN CONVERT(Tinyint,DATEPART(WEEKDAY, @StartDate)) in (1,7)
                                   THEN 1
                                   ELSE 0 
                                 END
             )
             
             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

Sample Output:

Next Steps:

To ensure that the calendar table is as efficient as possible, it’s important to set up appropriate indexes and constraints once the table grows considerably large.

Conclusion:

Creating a calendar table in SQL Server can greatly improve performance and simplify your code when joining data on date-related attributes in reporting, analytics, or OLTP use cases. These tables also make it easier for reporting engines to consume the data and increase maintainability by keeping a single copy of calendar data in one place, reducing the risk of coding errors when working with complex date calculations

You can also refer the following link for more idea https://www.sqlshack.com/designing-a-calendar-table/

You may also like the below articles:

Script To List SQL Agent Jobs and Schedules

Script to Change SQL Agent Job Schedule

Script to Find Agent Job in Execution