6

I want to add windows user to SQL users pragmatically. my problem is that I want to do this on systems that have SQL installed in mixed mode but I don't have username and password , I just know the instance name and I don't know how can I execute my script of adding windows user to SQL users in this situation.

I know the script is :

use master
GO

create login [<YourDomain>\User1] from windows;
GO

my connection string is :

ServerConnection myServerConnection = new ServerConnection(myinstancename);
Server myServer = new Server(myServerConnection);

SqlConnection scSqlConnction = new SqlConnection(myServerConnection.ConnectionString);

but when I create a server connection with my instance name, and want to execute script with related connection string I get the error of

login failed for user (windows user)

Shima.Y
  • 373
  • 4
  • 9
  • 18
  • You need to be able to log in to do anything. If you can't log in you're out of luck. – Nick.Mc Jan 11 '16 at 08:20
  • would you please help me by code ? creating a server connection with instance name isn't enough? – Shima.Y Jan 11 '16 at 08:23
  • You don't have access to the SQL Server so no amount of code will help - you can't log in. – Nick.Mc Jan 11 '16 at 08:23
  • 1. Can you confirm that 'User1' exists in 'YourDomain'? 2. Have you tried to add this User1 via the SQL Server Management Studio? 3. You could add your ConnectionString to the Question, maybe there is something wrong here. – fuchs777 Jan 11 '16 at 08:34
  • yes I can add this user via SQL Server Management Studio when I have Username and password but in many cases I dont have username and password and I want to create a database in windows mode so I need to add windows user to SQL Users – Shima.Y Jan 11 '16 at 08:48
  • I edited my question and added my connection string – Shima.Y Jan 11 '16 at 08:52
  • Can you execute any statement by your connection and your sqlconnection can be open or not? – Ahmed Galal Jan 11 '16 at 09:09
  • If you do not have a login (windows or SQL) for the SQL Server you can't add a database to it. You can install a new one but again you need windows access for that. – Nick.Mc Jan 14 '16 at 03:24

1 Answers1

12

You Can try this code Must be have UserName in your windows

Windows Login :

CREATE LOGIN [YourDomain\UserName] FROM WINDOWS WITH DEFAULT_DATABASE= master
GO
ALTER SERVER ROLE [sysadmin] 
ADD MEMBER [YourDomain\UserName]
GO

SQL Login

CREATE LOGIN UserName 
    WITH PASSWORD = N'Password' 
    MUST_CHANGE,
    CHECK_POLICY = ON;
GO
Ahmed Galal
  • 694
  • 7
  • 21