SQL Server - SERVER / DATABASE Level Fixed Roles and Permissions

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

Microsoft SQL Server 2017 and Azure SQL Database - Permissions Hierarchy

Permissions hierarchy explained well for the below area

SQL Server

• Server Level Permissions
• Database Level Permissions

Azure SQL

• Database Level Permissions - Outside the Database

DOWNLOAD PDF

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15

Locking Hierarchy

SQL Server uses multi level of locking to allow different locks on various level of objects.

1. Lower level of locking on RID or KEY
RID - Row at heap (Actually doesn't have clustered Index)
KEY - Row at Clustered Index

2. Higher level of locking on Database

Hierarchies:
- Database (Highest level of locking)
- Database File
- Object
- Extent
- Page
- RID Or KEY (Lowest level of locking)

Note: SQL Server automatically decides on what level of lock should be placed to minimize the locking overhead.

How to search a specific STRING in all TABLES in current DATABASE ?

Do you want to search a specific string in current database ? (In all the tables)

/*Creating a new Database*/
If DB_ID('SQLBuddy') Is Null

Create Database SQLBuddy

Go

/*Changing the Database context to "SQLBuddy" Database*/

Use SQLBuddy

Go

/*Creating Sample Tables: "Table1", "Table2", "Table3", "Table4"*/
If OBJECT_ID('Table1') Is Null
Create Table Table1

(

Id Int Identity(1,1),
Column1 Varchar(50),
Column2 NVarchar(50),
Column3 DateTime
)

Go

If OBJECT_ID('Table2') Is Null

Create Table Table2

(
Id Int Identity(1,1),
Column1 Int,
Column2 DateTime,
Column3 Char(50)
)

Go

If OBJECT_ID('Table3') Is Null

Create Table Table3

(

Id Int Identity(1,1),

Column1 Int,

Column2 DateTime

)

Go

If OBJECT_ID('Table4') Is Null

Create Table Table4

(
Id Int Identity(1,1),
Column1 NVarchar(100),
Column2 Varchar(50)
)

Go

/*Inserting Sample Data*/

Insert Table1(Column1, Column2, Column3) Values('MS SQL Server','SQL Administration',GETDATE())

Insert Table1(Column1, Column2, Column3) Values('MS SQL Server','SQL Developement',GETDATE())

Insert Table1(Column1, Column2, Column3) Values('Oracle','Oracle Administration',GETDATE())

Go

Insert Table2(Column1, Column2, Column3) Values(1,getdate(),'MS SQL')

Insert Table2(Column1, Column2, Column3) Values(2,getdate(),'MS Access')

Go

Insert Table3(Column1, Column2) Values(1,getdate())

Insert Table3(Column1, Column2) Values(2,getdate())

Go

 

Insert Table4(Column1, Column2) Values('MS SQL Server','BI')

Insert Table4(Column1, Column2) Values('MS SQL Server','Developement')

Go

/*Creating a stored procedure to findout the Matching Data from tables in current Database*/

If OBJECT_ID('Usp_Isavailable') Is Not null

Drop Proc Usp_Isavailable

Go

Create Proc Usp_Isavailable

(

@Table Varchar(100),

@Column Varchar(100),

@Search Varchar(1000),

@Available Varchar(100) Output

)

As

Begin

Set Nocount On

Declare @Query Varchar(1000)

Declare @Status Table(Available Varchar(100))

Select @Query = 'Select ' + @Column + ' from ' + @Table + ' where ' + @Column + ' like ''%' + @Search + '%'''

Insert @Status

Exec(@Query)

Select @Available = Available from @Status

End

Go

 
/*Creating stored procedure to identify/fetch the matching Columns & Tables in current Database*/

If Object_Id('Usp_FindString') Is Not Null

Drop Proc Usp_FindString

Go

Create Proc Usp_FindString

(

@Database Varchar(100) = NULL,

@TableName Varchar(100) = NULL,

@ColumnName Varchar(100) = NULL,

@SearchString Varchar(100)

)As

Begin

Set Nocount On

Create Table #Table_Column(TableName Varchar(100),ColumnName Varchar(100))

Create Table #Columns(TableSlno Int,ColumnSlno Int, TableName Varchar(100),ColumnName Varchar(100))

Create Table #Status(DatabaseName Varchar(100), TableName Varchar(100), ColumnName Varchar(100), MatchingValue Varchar(4000))

Insert #Table_Column

Select distinct Object_name(c.id) [Table], c.name [Column] from sys.syscolumns c join sys.types t on (c.xtype = t.system_type_id)

where t.collation_name Is Not null

and c.id >100

and Objectproperty(c.id,'IsUserTable') = 1

And Object_name(c.id) = ISNULL(@TableName, Object_name(c.id))

And c.name = ISNULL(@ColumnName, c.name)

Order by 1

Declare @Tables Int, @Seq Int, @Columns Varchar(4000), @Table Varchar(4000), @Script Varchar(MAX),@IsAvailable Varchar(100), @DatabaseName Varchar(100), @ColSeq Int, @ColTotal Int

;With CTEs
As

(

Select distinct o.TableName,o.ColumnName from #Table_Column o

)

Insert #Columns
Select Dense_Rank() Over(order by TableName),Dense_Rank() Over(Partition by TableName Order by ColumnName),TableName,ColumnName from CTEs

Select @Tables = Max(TableSlno) From #Columns

Select @Seq =1, @Columns ='', @Script ='', @DatabaseName = DB_Name(), @ColSeq = 1

While (@Seq <=@Tables)

Begin

Select @ColTotal = Max(ColumnSlno) From #Columns where TableSlno = @Seq

Select @ColSeq = 1

While(@ColSeq <= @ColTotal)
Begin

select @Table = TableName, @Columns = ColumnName from #Columns where TableSlno = @Seq And ColumnSlno = @ColSeq

EXEC Usp_Isavailable @Table,@Columns,@SearchString,@IsAvailable Output

If (@IsAvailable Is Not null AND @IsAvailable <>'')

Insert #Status Values(@DatabaseName,@Table,@Columns,@IsAvailable)

Select @IsAvailable = NULL

Select @ColSeq = @ColSeq + 1

End

Select @Seq = @Seq + 1
End

Select * from #Status

Drop Table #Table_Column
Drop Table #Columns
Drop Table #Status

End

Go

 

/*1. Searching a specific data("SQL") in all the tables & columns in current database*/
Exec Usp_FindString @SearchString = 'SQL'

Go

 

 

 

 

 

 

 

/*2. Searching a specific data("SQL") in all the tables in specific columns("Column1") in current database*/

Exec Usp_FindString @ColumnName ='Column1', @SearchString = 'SQL'

Go

 

 

 

 

 

/*3. Searching a specific data("SQL") in specific table("Table1") in specific columns("Column1") in current database*/

Exec Usp_FindString @TableName='Table1',@SearchString = 'SQL'

Go

 

 

 

 

 

/*4. Searching a specific data("SQL") in specific table("Table1") in specific columns("Column1") in current database*/

Exec Usp_FindString @TableName='Table1', @Columnname ='Column1', @SearchString = 'SQL'

Go