Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

need give SQL permission on the remote server

Posted on 2013-12-31
10
Medium Priority
?
286 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: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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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