Solved

need give SQL permission on the remote server

Posted on 2013-12-31
10
276 Views
Last Modified: 2014-01-21
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
Comment
  • 5
  • 4
10 Comments
 
LVL 4

Expert Comment

by:ItWorked
Comment Utility
Would like to know about....
structure of CSV.
Do you have permission on Remote server?

Is there are more then one remote server?
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
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
 
LVL 4

Expert Comment

by:ItWorked
Comment Utility
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
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
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
 
LVL 4

Expert Comment

by:ItWorked
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
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
 
LVL 4

Expert Comment

by:ItWorked
Comment Utility
Are you familiar with .NET? I can give you few hints on how to do so.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
Nope, But i know bit in powershell.
0
 
LVL 4

Accepted Solution

by:
ItWorked earned 500 total points
Comment Utility
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
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now