To grant db_reader/db_writer permission to all or particular user to DB by bulk /single

----------------------To grant db_reader permission to all user for particular DB by bulk

DECLARE @name VARCHAR(50) -- database name 

DECLARE @user sysname
DECLARE @login sysname
DECLARE @SQL nvarchar(max) = ''

DECLARE db_cursor CURSOR READ_ONLY FOR 

 select name from sys.syslogins where name not in ('sa','dbadmin') and name not like '%#%' and isntname=0

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 

SET @login=@name
SET @user=@name

SELECT @SQL = @SQL + '
    USE ' + QUOTENAME(NAME) + ';

    IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @user + ''')
    BEGIN
        CREATE USER ' + QUOTENAME(@user)
           + ' FOR LOGIN ' + QUOTENAME(@login)
           + ' WITH DEFAULT_SCHEMA=[dbo];
    END
    ELSE
    BEGIN
        EXEC sp_change_users_login ''Update_One'', ''' + @user + ''', ''' + @login + ''';
    END

    EXEC sys.sp_addrolemember ''db_datareader'', ''' + @user + ''';

   
'
FROM sys.databases
WHERE database_id > 4 AND state = 0 and database_id=15

EXEC sp_executesql @sql;
print @sql

   FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

----------------------To grant db_reader permission  particular user for particular /all DB


DECLARE @user sysname = 'testpub123';
DECLARE @login sysname = 'testpub123';
DECLARE @SQL nvarchar(max) = '';

SELECT @SQL = @SQL + '
    USE ' + QUOTENAME(NAME) + ';

    IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @user + ''')
    BEGIN
        CREATE USER ' + QUOTENAME(@user)
           + ' FOR LOGIN ' + QUOTENAME(@login)
           + ' WITH DEFAULT_SCHEMA=[dbo];
    END
    ELSE
    BEGIN
        EXEC sp_change_users_login ''Update_One'', ''' + @user + ''', ''' + @login + ''';
    END

    EXEC sys.sp_addrolemember ''db_datareader'', ''' + @user + ''';

   
'
FROM sys.databases
WHERE database_id > 4 AND state = 0 and database_id=24;

EXEC sp_executesql @sql;
print @sql

--select * from sys.sysusers

--sp_helpdb Demo_DB1

--select * from sys.databases

How to solve User database listed in system databases

Due to some replication troubleshooting situation we ran some commands wrongly due to  we can't add publication in replication as it shows invalid object name of system replication tables. now user database shows in system databases tab as below

how to solve it to bring back to user database section.

solution:

it is due to if you run this below command user database replication tables could be added but his properties of is_distributor value change from 0 to 1.

as below

to solve this we need to run this below command in master

now u can check database listed in user database as see below

thats it . we bring user database to user database section from system database as see below

script:

select is_distributor,* from sys.databases where is_distributor=1

--Exec sp_adddistributiondb 'pubs'-------------Dont run it on prod server

--Exec sp_dropdistributiondb pubs

Top 10 New Features Of SQL Server 2019

It is no secret to anyone that software release cycles are more aggressive than ever. Now that the cloud has become ubiquitous as a strategic component of IT services, we are spoiled by a constant release of new features and services available to us. It follows then that on-premises or “retail-delivered” software is also getting more frequent releases as customer expectations grow. This is how over the last four years we have had three major releases of SQL Server: 2016, 2017 (with the big milestone of SQL on Linux) and now SQL 2019.

SQL 2019 is shaping up to be a very powerful evolutionary step to the features that were introduced in SQL 2017, as well as bringing its own set of brand new capabilities that continue to position the product as a “one-stop” software platform for all your data needs. With this in mind, and considering how much is built into the product nowadays, I have compiled this list of my top favorite features of SQL 2019! Now, of course, this is my blog and my opinion and the list is not exhaustive. For the full list, refer to the Microsoft documentation. As you will see, my favorites are mostly technical features that will help developers and DBAs in their daily functions. Also, a quick disclaimer – all the images are from Microsoft’s own SQL Server documentation.

I am sure that your top 10 is likely to be very different than my top 10 so make sure you check it all out in detail! Here is my list:

1. Intelligent Query Processing Enhancements

What they are: This is a set of enhancements that affect the behavior of the Query Optimizer, the component inside SQL Server that generates the execution plans for queries. This includes dynamic memory grants for rowstore tables, table variable deferred compilation, batch mode on rowstore and more.
Why this matters: These are all behind-the-scenes improvements on the Query Optimizer that will improve the quality of the plans for all applicable queries. This means better performance overall after doing the upgrade.
Cost of adoption: Test your problem queries on a development instance to verify the improvements. That’s pretty much it. It’s one of those great improvements that works with no big changes required from the customer (I really like those as you will see from this list).

2. Accelerated Database Recovery (ADR)

What this is: This is a completely new way for SQL Server to perform database recovery in the event of a transaction rolled back, an instance restart or an Availability Group failover. Instead of the sometimes unpredictable and less than desired time spent waiting for the database recovery to run, the SQL team has redeveloped how recovery works and has dramatically decreased how long this process takes.
Why this matters: Anyone who has had to wait for a production SQL Server instance to rollback a long transaction or who has had an unfortunate crash during a large data operation knows how painful it is to just wait for recovery to be finished so you can get back in business. ADR will provide benefits for SLA and RTO all around.
Cost of adoption: None. Activate it and enjoy (one of those again!).

3. AlwaysEncrypted With Secure Enclaves

What this is: This is the next version of AlwaysEncrypted, the encryption technology introduced in SQL Server 2016 that allows transparent column encryption without giving administrators access to the decryption keys. One drawback of the first implementation is that due to SQL Server not being able to decrypt the data, the queries on the SQL side couldn’t do any computations or manipulation of the actual column values. Using the new Secure Enclaves technology, SQL Server can now securely encrypt a portion of memory to perform computations on these encrypted columns without the unencrypted values ever being exposed to the rest of the processes (or administrators).
Why this matters: Security matters and performance matters, as well. Database servers are best equipped for processing large amounts of data so being able to have AlwaysEncrypted and also do complex manipulations is the best of both worlds.
Cost of adoption: If you are already using AlwaysEncrypted then no big changes are necessary other than reconfiguring and re-encrypting the columns of interest. If you are not using AlwaysEncrypted, then now is a good time to investigate, test this feature and see if it’s a good fit for your security requirements.

4. Memory-Optimized Tempdb Metadata

What this is: The SQL team has made optimizations to the tempdb code so that some of the metadata that can be a bottleneck on tempdb heavy systems can rely completely on memory and be optimized for RAM access.
Why this matters: Large volume, large scale environments that use a lot of tempdb run into this type of bottleneck. Usually, this requires some sort of refactoring to alleviate the use of tempdb. With this feature in place, it is possible to enable the metadata to sit in memory and be optimally accessed. This way will get rid of these issues.
Cost of adoption: Activate the feature and verify there is an improvement, not much more than that (another one!).

5. Query Store Custom Capture Policies

What this is: Query Store is a great performance tuning and trending tool that allows for storing, measuring, and fixing plan regressions inside a SQL Server database. One downside of using it though is that sometimes it can store too much information, even for queries that the DBA might not be interested in or for queries that were part of a system utility or monitoring tool. This new capability of custom policies means you can fine-tune exactly which queries should be tracked based on their execution statistics such as how often they run, the CPU they consume, and more.
Why this matters: Query Store is a great feature but it is not so useful if it consumes a lot of resources and if it’s too bloated to be effective for the DBA. This feature will allow fine-tuning so that it is always efficient and lean and easy to use for fixing plan issues.
Cost of adoption: You will need to sit down and see what type of execution conditions you want to use as a filter for your Query Store. Implementing it is just a matter of using new syntax after that.

6. Verbose Truncation Warnings

What this is: Every single T-SQL developer knows the pain and grind of getting a truncation error. Some value somewhere does not fit under a new data type, but you don’t get any details at all. Then it’s a matter of trial and error until you finally figure out which value is the offending one. Not the best experience for what should be a straightforward issue to solve!
Why this matters: Because mental sanity matters. These new messages give you all the details of the data truncation issue so you can just fix it and get on with your day!
Cost of adoption: None. It’s the new default (loving all of these)!

7. Resumable Index Build

What this is: SQL Server now has the capability to stop an index rebuild operation in progress, keep the work that has been done so far and resume at some other point in time.
Why this matters: For some folks, index rebuilds are still necessary and they consume so many resources that even with the ONLINE option, they still have to deal with the reality of maintenance windows. The problem, however, is what happens if you run out of time during your maintenance window? Previously, you would need to cancel your rebuild, wait for a potentially long recovery and then start again from scratch. This new feature gets rid of these problems!
Cost of adoption: Change your index scripts to use the new RESUMABLE option. Pretty easy (and another one!).

8. Data Virtualization With Polybase

What this is: Polybase is SQL Server’s module that allows fast and parallel T-SQL queries that can go out into external storage (usually HDFS on-prem) and bring the results seamlessly as a T-SQL result set. With SQL 2019, Polybase is getting expanded to support Oracle, Teradata, MongoDb and more.
Why this matters: Data integration is always a challenge and with ever-growing data sets, performance can become an issue. Trying to query and move large amounts of data through a linked server has always been painfully slow as it is not really optimized for this job. Polybase allows SQL Server to become the data hub of an organization by leveraging T-SQL skills and keeping performance as a top priority at the same time.
Cost of adoption: This one definitely requires some work as you would want multiple Polybase nodes, set up the connectivity to your other database platforms and then test the performance of those queries.

9. Last Actual Execution Plan DMF

What this is: This is a new Dynamic Management Function called sys.dm_exec_query_plan_stats that will track the last ACTUAL execution plan for a query if you enable the lightweight query profiling feature (which you probably should do).
Why this matters: Previously, grabbing an actual query plan required either a Profiler trace, an XEvents trace, or a call to an ephemeral DMF that would lose its contents when the query was done executing. Not the easiest or most convenient mechanisms to do what is pretty much a critical step in any sort of production performance problem scenario.
Cost of adoption: Enable the setting, use the DMF (and another one to just use!).

10. Multiple Internal Performance Improvements

What this is: There are multiple internal performance improvements done by the SQL team for this release. There is a new index optimization for indexes that have a sequential key, temp table recompilation improvements, improved indirect checkpoint scalability, and more.
Why this matters: These are all performance improvements that come “out-of-the-box”, optimize common SQL Server processes and require no effort from the client to benefit from them.
Cost of adoption: 

Msg 7321, Level 16, State 2, Line 68 An error occurred while preparing the query

Msg 7321, Level 16, State 2, Line 68
An error occurred while preparing the query "

Solution:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/083229e7-58f2-44cf-ae5d-c9acf366818c/sql-2005-ldap-query-error-msg-7321-level-16?forum=transactsql

It normally succeedes test connection generally but when you run query using this linked server to pull data from AD(Active Directory) server it will throw above issue. so issue will be between db server of linked server and AD server.

we need to make sure logins should be same on both AD server and db server
of linked server created.

LDAP means Lightweight Directory Access Protocol


First execute your query:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject'

, 'adsdatasource'

GO

Like daw21 explain before follow the detail steps:

Second step go to the SqlServer management Studio and edit manually the Linkserver name ADSI:

go to Server Objects/Linked Servers/  and right click on ADSI  select the option Properties and then go to Security.

Select the option :         Be made using this security context:

and there fill the user  and Password about the valid user with permission into Active Directory, not forget before the domain\user.



(or)

First you have to make sure that Ad Hoc Distributed Queries is enabled ...

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Second you have to make sure that you specify a security context that has access to AD INCLUDING the DOMAIN
Third you should put a row limit (e.g. SELECT TOP 100) in to avoid the issue where you hit the 1000 row limit so
SELECT



TOP 100 *

FROM



OpenQuery(ADSI, 'SELECT sAMAccountName FROM ''LDAP://DC=core,DC=acllab,DC=net'' WHERE objectCategory=''user'' ')

To change file name of text files from SQL QUERY

----------------To change file name of text files from SQL QUERY-------------------------------------------------------------

create table #test(col   varchar(1000))
create table #files(name varchar(1000),rename varchar(1000))

Declare @path as varchar(500)='\\sharepath\filesfolder\test\'
Declare @ssql as varchar(500)='dir ' + @path
Declare @col as varchar(500)=''
Declare @col1 as varchar(500)=''

insert into #test
Exec xp_cmdshell @ssql


--------Tables

insert into #files
select name=reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),
rename=REPLACE(reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),'.Table','')

-------SPs

--insert into #files
--select name=reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),
--rename=REPLACE(reverse(left(reverse(col),PATINDEX('% %',reverse(col)))),'.StoredProcedure','')


from #test where PATINDEX('%.%',col) > 0

delete
from #files where  name in (' .',' ..')

select @col =name , @col= rename
from #files

while (1=1)
Begin
select  top 1 @col =name , @col1= rename from #files
if @col = ''
begin
break
return
End

set @ssql = 'rename  ' + @path+'\'+ ltrim(rtrim(@col))+' '+ltrim(rtrim(@col1))+''
print @ssql

Exec xp_cmdshell @ssql
delete  from #files  where @col =name
set @col = ''
End


------------file name be like dbo.Test2.Table
-------output of result dbo.Test2

Fix issues of line split from SQL to Excel while copy and paste

---------------------------------Fix issues of line split from SQL to Excel while copy and paste

SELECT replace(replace(columnwhichhaveissueswhilecopy, char(10), ''), char(13), '')
FROM [dbname].[dbo].[tablename]

Ref:
https://www.mssqltips.com/sqlservertip/3416/line-split-issues-when-copying-data-from-sql-server-to-excel/