Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

need give SQL permission on the remote server

Posted on 2013-12-31
10
Medium Priority
?
287 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:Hiran Desai
ID: 39749714
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
ID: 39749718
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:Hiran Desai
ID: 39749734
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39749922
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:Hiran Desai
ID: 39750675
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
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39750723
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:Hiran Desai
ID: 39750827
Are you familiar with .NET? I can give you few hints on how to do so.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39750863
Nope, But i know bit in powershell.
0
 
LVL 4

Accepted Solution

by:
Hiran Desai earned 2000 total points
ID: 39750905
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.
ID: 39763825
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

916 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