Showing posts with label sql server. Show all posts
Showing posts with label sql server. Show all posts

Wednesday 11 November 2020

How To create SSIS user in Azure SQL server

I've assumed you have already configured domain in azure and have created a domain user.

Create Application in app registration  

First of all Create an app registration [myssissqlserver01]


Set up Active directory Admin in sql server

Now Go to the sql server (not the database) and on settings menu, click Active directory Admin



Click Set Admin and add your domain account. (in case you don't have domain configured , Configure a new domain and create a domain user first)

Connect to the database using MS Sql server management studio

Once admin is set , Open MS Sql server management studio and connect to the database.



Since we have added user to active directory admin, the user will have access to master database. So we can choose default database to connect, 


Create a user in ssis and add to a suitable role

Create a new query window selecting SSISDB database

execute the below queries. Note that the user name should be identical to the Application name created in App registration section . [ myssissqlserver01]

create user [myssissqlserver01] from external provider;

exec sp_addrolemember [db_owner],[myssissqlserver01];


Monday 17 August 2020

Create Azure SQL User and add a sql role

I wanted to add a new sql user "sqluser" in Azure SQL server to access a database "sanjeeb". 

Steps to create a new SQL user. 

1) Connect to the Azure sql server with existing user using Microsoft SQL Server Management studio

2) Open new query window under Master database


3) Run the below script
    
    CREATE LOGIN [sqllogin] WITH PASSWORD = 'verystrongpassword#1' 
   



4) Open new query window under database "sanjeeb"

5) Run the below script

CREATE USER [sqluser]
FOR LOGIN [sqllogin]
WITH DEFAULT_SCHEMA =  dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'sqluser'
GO

6) You've now created a user login "sqllogin", user account "sqluser" and added the user to "db_owner role"



7) Connect to the database using the new user login account. 

login: sqllogin
password:verystrongpassword#1


8) Click Options and select Connection properties tab

9) input Connect to database = sanjeeb

10) Click connect

Wednesday 5 August 2020

Insert Identity Column in a table

 insert an identity column in that table by using the following code:


MSSQL Remove duplicate Rows

  1. Select the duplicate key values into a holding table. For example:

        SELECT col1, col2, col3=count(*)
        INTO holdkey
        FROM t1
        GROUP BY col1, col2
        HAVING count(*) > 1
  2. Select the duplicate rows into a holding table, eliminating duplicates in the process. 
    For example:
           SELECT DISTINCT t1.* 
           INTO holddups
           FROM t1, holdkey 
           WHERE t1.col1 = holdkey.col1 
           AND t1.col2 = holdkey.col2
  3. At this point, the holddups table should have unique PKs, however, this will not be the case if t1 had        
    duplicate PKs, yet unique rows (as in the SSN example above). Verify that each key in holddups is unique,
    and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile 
    which of the rows you wish to keep for a given duplicate key value. For example, the query:
           SELECT col1, col2, count(*)
           FROM holddups
           GROUP BY col1, col2
    should return a count of 1 for each row. If yes, proceed to step 5 below. 
    If no, you have duplicate keys, yet unique rows, and need to decide which 
    rows to save. This will usually entail either discarding a row, or creating
    a new unique key value for this row. Take one of these two steps for each 
    such duplicate PK in the holddups table.
    
  4. Delete the duplicate rows from the original table. For example:
         DELETE t1
         FROM t1, holdkey
         WHERE t1.col1 = holdkey.col1
         AND t1.col2 = holdkey.col2  
  5. Put the unique rows back in the original table. For example:
         INSERT t1 SELECT * FROM holddups
Another Way:
delete from table1
WHERE id IN (
SELECT t1.id
FROM table1 t1, table1 t2
WHERE t1.name = t2.name AND t1.id > t2.id

Generate SQL script from entity framework using powershell/visual studio

to run the below command , start PowerShell in visual studio. Select the database project. In PowerShell:   Script-Migration this command wi...