Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

need give SQL permission on the remote server

I have a CSV file which has servername ,database, ad group and role.I need a sample script which will pick up the these details one by one from the csv file and have to provide the required permission on the remote server.
How to do it?
0
VIVEKANANDHAN_PERIASAMY
Asked:
VIVEKANANDHAN_PERIASAMY
  • 5
  • 4
1 Solution
 
Hiran DesaiSolution ArchitectCommented:
Would like to know about....
structure of CSV.
Do you have permission on Remote server?

Is there are more then one remote server?
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
structure of CSV.

servername ;database;     ad group;role
AAA             ;  DB_NAME ;AD_NAME; DB_READER


Do you have permission on Remote server?
Yes, i'm sysadmin and have local admin of the server.

Is there are more then one remote server?Yes, there are 100 rows like above.
0
 
Hiran DesaiSolution ArchitectCommented:
Can you please elaborate more....this is making confusion..you want to have permission on server which is in CSV file?

What permission? Permission on SQL Server or Windows Server?

User mentioned in CSV are AD user?
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Can you please elaborate more....this is making confusion..you want to have permission on server which is in CSV file?
Nope, i 'm a Database admin, in the csv FILE there will be some other new ad groups which needs to be added.So want to automate this process. There are around 100 server,i have add new users/ad group.It's hectic.So seeking help of automating it.

What permission? Permission on SQL Server or Windows Server?
Permission on SQL server. Role will be mentioned in the CSV respectively.

User mentioned in CSV are AD user?

Yes, it AD group.

E.g
servername ;   database;     ad group;role
remoteserver1  ;  ABC ;DOMAIN\VIVEK; DB_READER
remoteserver2  ;  DFG ;DOMAIN\ITADMIN; DB_OWNER


CSV  file will have like above details, now on remoteserve1 ,on abc database user domain\vivek needs to be added with db_reader permission.
likewise it should do for rest of the content.
0
 
Hiran DesaiSolution ArchitectCommented:
Ok! I understood and I assume that you do have an Administrative/appropriate account on remote server(s) (such as remoteserver1,remoteserver2).

You need to have knowledge of some sort of programming knowledge. The reason is you need to execute queries on remote server.(if this is same server, that can be done with triggers but I assuming this not the case).


what needs to be done is you import your CSV to your application.
Which will iterate on each remote computer(details...this means you need to provide credential details of each remote sQL server to get connected with it).

one it's connected in background it will execute following query from the application as
CREATE LOGIN [domain\user] 
FROM WINDOWS
WITH DEFAULT_DATABASE = [DBName];
GO
-- Add User to first database
USE [DBName];
CREATE USER [user] FOR LOGIN [domain\user];
EXEC sp_addrolemember '[DbRole]', '[user]'

Open in new window


Let me know if you need more information.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Yes, i know the above steps. But my queries is, how pickup the data from csv file and trigger those queries on the remote machine? This is my actual question.
0
 
Hiran DesaiSolution ArchitectCommented:
Are you familiar with .NET? I can give you few hints on how to do so.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Nope, But i know bit in powershell.
0
 
Hiran DesaiSolution ArchitectCommented:
Got something...used my knowledge of programming...
This may have some error..but check it with Powershell syntax

// reades content of file and sets to $a
$a = Get-Content C:\Scripts\Test.txt

//Split the content with lines....
$a.Split("YourLineSeprator") | ForEach 
{
	//$b will be array that contains RemoteServerName,DatabaseName,Ad_GroupName,Role
    $b=$_.Split("YourValueSeprator")
	$os = Get-WmiInfo Win32_OperatingSystem
	[assembly.reflection]::loadwithpartialname('System.Data')
	$conn = New-Object System.Data.SqlClient.SqlConnection
	
	//ConnectionString to Remote Server :: "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI;"
	//Conn String if you have credentials :: "Data Source=ServerName;Initial Catalog=DatabaseName;uid=username;pwd=password"
	
	$conn.ConnectionString = "Data Source=$b[0];Initial Catalog=$b[1];Integrated Security=SSPI;"	
	$conn.open()
	$cmd = New-Object System.Data.SqlClient.SqlCommand
	$cmd.connection = $conn
	//For below line (?) is Username for which you want to set permission..You haven't specified which in your question.
	$cmd.commandtext = "CREATE LOGIN $b[2]\(?) FROM WINDOWS WITH DEFAULT_DATABASE = $b[1];GO USE $b[1]; CREATE USER (?) FOR LOGIN $b[2]\(?); EXEC sp_addrolemember '$b[3]', '(?)'" 
	$cmd.executenonquery()
	$conn.close()
	
}

Open in new window

0
 
Jim P.Commented:
The other question is what account is the SQL Server service and agent running under? And does that (those) accounts have permissions to the remote folder?

Also are you using a UNC (\\ServerName\ShareName) or a mapped drive (X:\ShareName)? SQL Server doesn't recognize mapped drives.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now