SQL Server Database Permission Scripts

-

#1 - Script to get list of all database name and its user in SQL Server

  1. USE MASTER  
  2. GO  
  3.   
  4. SELECT SL.dbname AS 'Database Name',SL.name AS 'User Name',SP.type_desc AS 'Login Type', 
  5. SL.denylogin, SL.hasaccess, SL.isntname, SL.isntname, SL.isntgroup, SL.isntuser, SL.sysadmin,  
  6. SL.securityadmin, SL.serveradmin, SL.setupadmin, SL.processadmin, SL.diskadmin, SL.diskadmin,  
  7. SL.dbcreator, SL.bulkadmin FROM sys.server_principals AS SP  
  8. INNER JOIN sys.syslogins AS SL ON SP.SID = SL.SID  

#2 - Script to get list of all database users and their roles in SQL Server

  1. Use Master  
  2. GO  
  3.   
  4. exec sp_msForEachDb ' use [?]  
  5. select db_name() as [Databast Name], r.[name] as [Role], p.[name] as [Member Name],  
  6. p.[default_schema_name] as [Schema],p.[principal_id] as [Principal Id]  
  7. from  
  8. sys.database_role_members m  
  9. join  
  10. sys.database_principals r on m.role_principal_id = r.principal_id  
  11. join  
  12. sys.database_principals p on m.member_principal_id = p.principal_id'  

#3 - Script to get list of users and their permission with all stored procedure in SQL Server database

  1. use <<databasename>>  
  2. GO  
  3.   
  4. select sys.schemas.name 'Schema'  
  5. , sys.objects.name 'Stored Procedure'  
  6. , sys.database_principals.name username  
  7. , sys.database_permissions.type permissions_type  
  8. , sys.database_permissions.permission_name  
  9. , sys.database_permissions.state permission_state  
  10. , sys.database_permissions.state_desc  
  11. , state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS  
  12. from sys.database_permissions  
  13. join sys.objects on sys.database_permissions.major_id = sys.objects.object_id  
  14. join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id  
  15. join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id  
  16. Where sys.objects.type IN ('P')  
  17. order by 1, 2, 3, 5  

 Tested in SQL server 2008 R2, 2008, 2012.

Comments

Post a Comment

Popular Posts

Contact Application Using ASP.NET Core Web API, Angular 6.0, And Visual Studio Code - Part One

-
Image
We will develop a contact application using ASP.NET Core Web API, Angular 6.0, and Angular Material UI as shown in the below screenshots.  We will use Visual Studio code (VS code) for the development editor.  I have divided contact application development into two articles, Contact Application using ASP.NET Core Web API, Angular 6.0, and Visual Studio Code Part – 1 In the article, we will set up ASP.NET Core Web API project, and develop the Web API controller for contact CRUD operations. Contact Application using ASP.NET Core Web API, Angular 6.0, and Visual Studio Code Part – 2 In the article, we will setup Angular 6 within ASP.NET Core Web API Project, and develop the contact form & list component using Angular Material UI. So, starting with this article, first, we will see how we can create ASP.Net Core Web API Project. We have the following prerequisites, Visual Studio Code (VS code) editor Please see this  article  to see how we can setup Visual Stud
Read more

Contact Application Using ASP.NET Core Web API, Angular 6.0, And Visual Studio Code - Part Two

-
Image
In this article, we are going to learn about how we can setup Angular 6.0 in ASP.Net Core Web API project and develop the contact list & form component using Angular Material UI. In the Part 1 article, we set up ASP.NET Core Web API and developed the API for contact CRUD operation. Please go through  Contact Application using ASP.NET Core Web API, Angular 6.0, and Visual Studio Code Part 1  once before proceeding with this article. To setup Angular 6.0 in the project, first, we have to install NodeJS in the development environment. We can download NodeJS installer from  NodeJS website . and then check the version on NodeJS as below: Now, let us globally install Angular CLI package by enter “npm install –g angular/cli@6.0.0”, as shown in below screenshot Scaffold Angular To scaffold Angular, enter “ng new Contact-App --skip-install” command in VS code terminal as below. Here, --skip-install option is used to skip installation of the npm packages. And Contact-
Read more

Send an Email Reminder Notification Based on an Expiration Date using Power Automate

-
Image
Introduction    In this article, we are going to create an email reminder notification based on an expiration date using Power Automate. We will create a flow that’s run daily that reads & filters data from a SharePoint list that has list items that expire in the next 5 days. Then it will send an expiration notification email to a particular user.   SharePoint List - "ProductSales"     Columns  Type  Title  Text  ExpiryDate  DateTime   Let’s log in to Office.com with office 365 credentials. Go to the “Power Automate” tile and click on it.   It will be redirected to Power Automate Page. Click on Create button from left navigation.   Click on the scheduled flow tile. It will open the popup.       Create Recurrence Flow Enter the flow name and select a specific time when we want to schedule this workflow. Then, click on the create button. It will open the Edit Flow page where we can see a Recurring Action. Now we are f
Read more

MySQL Data Access API Development Using Express.JS, Node.JS

-
Image
In this article, we are looking into the introduction of Node.JS and Express.JS and then, I am going to create a simple MySQL data access API using Express.js, NodeJS. As we all know, Node.JS is the most trending open source technology that’s directly or indirectly partitioning into advanced web development, and let’s have lookup what is Node.JS? Node.JS Definition Node.JS is a platform that provides environment (outside the web browser) to execute JavaScript and which is built on Chrome's V8 JavaScript engine developed by Ryan Dahl. Why Node.JS? NodeJs processing model depends on a single thread with event looping. When clients send HTTP requests to NodeJs server, the Event loop is woken up by OS. It will pass the request and response objects as JavaScript closures to worker functions with callbacks. For long-running jobs like I/O operations running on non-blocking worker threads, when the job is complete, responses are sent to the main thread via a callback
Read more

Chat Application using Angular 8, Asp.net Core 2.2.0, Signal R 1.1.0

-
Image
In this article, we are going to create a simple chat application using Angular 8, Asp.net Core 2.2.0, Signal R 1.1.0 as shown below: We install above all prerequisite following in our development machine: Prerequisite .Net Core SDK 2.2.0  – download from  here . Nodejs 10.15.3  – download from  here . Angular CLI 8.0  – Install angular command package by executing this command: “ npm install -g @angular/cli@8.0.0 ” Setup Asp.net Core with Signal R Project: open a command prompt and enter create asp.net core web project as below: Install the following NuGet package in our project: Microsoft.AspNetCore.SignalR Microsoft.AspNetCore.SpaServices.Extensions Call SignalR in the “Startup.ConfigureServices” method to add SignalR services: services.AddSignalR();   Create MessageHub Class: SignalR makes real-time client-to-server and server-to-client communications possible and it will call methods to connect clients from a server using Sign
Read more

ReactNative FlatList

-
Image
Introduction: React Native List show has different way to represent, react native listview either FlatList or SectionList. Flatlist is term used for static data just like array of list to view. Process: index.js import React, { Component } from 'react'; import { AppRegistry, FlatList, StyleSheet, Text, View } from 'react-native'; export default class FlatListBasics extends Component {   render() {     return (       <View style={styles.container}>         <FlatList           data={[             {key: 'Devin'},             {key: 'Jackson'},             {key: 'James'},             {key: 'Joel'},             {key: 'John'},             {key: 'Jillian'},             {key: 'Jimmy'},             {key: 'Julie'},           ]}           renderItem={({item}) => <Text style={styles.item}>{item.key}</Text>}         />       </View&
Read more

Getting Start With Data Analysis Using SSAS Tabular Modeling In Excel - Part Two

-
Image
In this article, we are going to see how business users can utilize the tabular model for data analysis and visualization in Excel. This article depends on Part I. So, it would be great if you first go through this: Getting started with data analysis using SSAS Tabular Modeling in Excel - Part I We will see how we can create SSAS tabular model and setup measure, roles, calculated columns, hierarchies in tabular modeling. Now, we created a Tabular Model and deployed it on the analysis server. Let’s open Excel. Go to Data Tab, click on Get Data Option from a ribbon. Go to From Database, Analysis services. It will open Data connection wizard, enter server & credential details. Click on next. Select Tabular Model Database. Click on next. Now, Save Data Connection and Click on finish. Go to Insert Tab, Click on Pivot Table option from the ribbon. Select External Data Source. Click on choose connection. We can see our saved data connection
Read more

Contact application - Upgrade Asp.net Core 2.0 to 2.1

-
Image
In this article, we will look into steps for contact application - upgrade Asp.net Core 2.0 to 2.1, please see my previous articles about how we developed contact application: ContactApplication using ASP.NET Core Web API, Angular 6.0, and Visual Studio CodePart One: In the article, we will set up ASP.NET Core Web API project, and develop the Web API for contact CRUD operations. ContactApplication using ASP.NET Core Web API, Angular 6.0, and Visual Studio CodePart Two: In the article, we will setup Angular 6 within ASP.NET Core Web API Project, and develop the contact form & list component using Angular Material UI that will consume Web API which we have created in Part One. ContactApplication Azure Deployment: In this article, we are going to deploy/host a contact application with Visual Studio code to Azure web apps. Please note:   Previously we have used Asp.net Core 2.0.3 for contact application, now upgrade to Asp.net Core 2.1.5. This is download link for Dot
Read more

Send Email With SharePoint Lookup Columns Data Using Power Automate

-
Image
  Introduction   In this article, we are going to send an email with SharePoint Lookup Column data using Power Automate. We will create a flow that’s run weekly that fetches data from a SharePoint list which contains lookup columns. We will use the “Apply each” action to get lookup records.   SharePoint Parent List - "ProductSales"   Columns Type Title Text ExpiryDate DateTime SalePersonName LookUp (SalePerson List)   SharePoinChild List - "SalePerson"   Columns Type Title Text   In the above lists we have SalePersonName column which is actually the Title column from SalePerson List. When we “Parse Json” data in the above type of data we get two title fields in the dynamic column section. And sometimes workflow fails due to a null record in lookup column. Let’s see the data in the list as below,     Log in to Office.com with Office 365 credentials. Go to the “Power Automate” tile and click on it. It will be redirected to the Power Automate Page. Click on the Create
Read more

Typescript Basics

-
Nowadays, there are so many JavaScript frameworks (Angular, React) written and used to develop frontend UI by Typescript. It very important to every developer has very basic knowledge about Typescript. So, in this article we will look into Typescript Basics for getting started with it. Pre-requisite:   Basic knowledge of JavaScript, OOPs concept. What is Typescript? The Typescript language is a typed super set of JavaScript, means any program written using Typescript Language will be compiled into plain JavaScript. What does Typescript solve over JavaScript? And Why Typescript Language is so much popular among Developers? Types & Equality Scope OOPs Implementation Module management Installing Typescript: There are two basic ways to install Typescript: Via NPM (Nodejs Package Manager) Typescript’s Visual Studio plugins. Please this  link  for more information For via Npm, enter this command in command prompt: npm install -g typescript   save fi
Read more