Solved

need give SQL permission on the remote server

Posted on 2013-12-31
10
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 4

Expert Comment

by:ItWorked
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:ItWorked
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:ItWorked
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:ItWorked
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:
ItWorked earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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