SQL Server Logon

  • Thread starter Thread starter tc
  • Start date Start date
T

tc

Does anyone have an example of how to connect to an SQL server and create a
new database, then add a user and assign permissions? One problem is that
users may be user authenticated, not Windows authenticated.

Thanks.
 
tc said:
Does anyone have an example of how to connect to an SQL server and create
a new database, then add a user and assign permissions? One problem is
that users may be user authenticated, not Windows authenticated.

Thanks.
Click to expand...

Do you want to do this programmatically, through scripts or via SQL Server
management tools? In any case, please tell us which version of SQL Server
(2000, 2005, etc) you are using as techniques would be different.
 
In VB.net
Provider:
SQL2k5 Username / Password:
Provider=SQLNCLI;Server=HostOrIP\Instance;Database=DatabaseName;Uid=myUsername;Pwd=myPassword;
SQL2k5 Trusted Connection:
Provider=SQLNCLI;Server='HostOrIP;Database=DatabaseName;Trusted_Connection=yes;
SQL2k Username / Password:
Provider=sqloledb;Data Source=HostOrIP;Initial Catalog=DatabaseName;User
Id=myUsername;Password=myPassword;
SQL2k Trusted Connection:
Provider=sqloledb;Data Source=HostOrIP;Initial
Catalog=DatabaseName;Integrated Security=SSPI;

Dim SQLServer As New Data.OleDb.OleDbConnection
SQLServer.ConnectionString =
"Provider=SQLNCLI;Server=primary\sql2k5;Database=master;Trusted_Connection=yes;"
SQLServer.Open()
Dim Command As New Data.OleDb.OleDbCommand
Command.Connection = SQLServer
Command.CommandText = "Create Database test123"
Command.ExecuteNonQuery()
Command.CommandText = "Use test123"
Command.ExecuteNonQuery()
Command.CommandText = "Create Table test (id int identity(1,1),test
nvarchar(50))"
Command.ExecuteNonQuery()

Command.CommandText = "CREATE LOGIN [primary\administrator] FROM
WINDOWS"
Command.ExecuteNonQuery()
Command.CommandText = "CREATE USER [PRIMARY\administrator] FOR LOGIN
[PRIMARY\administrator] WITH DEFAULT_SCHEMA=[dbo]"
Command.ExecuteNonQuery()
Command.CommandText = "Grant ALL to [primary\administrator]"
Command.ExecuteNonQuery()

Command.CommandText = "CREATE LOGIN [test] with Password='test'"
Command.ExecuteNonQuery()
Command.CommandText = "CREATE USER [test] FOR LOGIN [test] WITH
DEFAULT_SCHEMA=[guest]"
Command.ExecuteNonQuery()
Command.CommandText = "Grant SELECT to [test]"
Command.ExecuteNonQuery()

SQLServer.Close()

'SQL2k5 Username / Password:
Provider=SQLNCLI;Server=HostOrIP\Instance;Database=DatabaseName;Uid=myUsername;Pwd=myPassword;
'SQL2k5 Trusted Connection:
Provider=SQLNCLI;Server='HostOrIP;Database=DatabaseName;Trusted_Connection=yes;

'SQL2k5 Username / Password: Provider=sqloledb;Data Source=' SQL2k5
Username / Password: ;Initial Catalog=DatabaseName;User
Id=myUsername;Password=myPassword;
'SQL2k5 Trusted Connection: Provider=sqloledb;Data Source=' SQL2k5
Username / Password: ;Initial Catalog=DatabaseName;Integrated Security=SSPI;

Creates a SQL2k5 Database named test123 on primary (instance sqlsk5).
creates a table (test) with 2 fields (id integer as identity and test as
nvarchar). creates 2 user (1 windows, 1 sql) and logins to the new database.
grant full access to the windows- and only-select access to sql-user.

thats all.
 
Many thanks, will start playing tomorrow!

Matthias Vastring said:
In VB.net
Provider:
SQL2k5 Username / Password:
Provider=SQLNCLI;Server=HostOrIP\Instance;Database=DatabaseName;Uid=myUsername;Pwd=myPassword;
SQL2k5 Trusted Connection:
Provider=SQLNCLI;Server='HostOrIP;Database=DatabaseName;Trusted_Connection=yes;
SQL2k Username / Password:
Provider=sqloledb;Data Source=HostOrIP;Initial Catalog=DatabaseName;User
Id=myUsername;Password=myPassword;
SQL2k Trusted Connection:
Provider=sqloledb;Data Source=HostOrIP;Initial
Catalog=DatabaseName;Integrated Security=SSPI;

Dim SQLServer As New Data.OleDb.OleDbConnection
SQLServer.ConnectionString =
"Provider=SQLNCLI;Server=primary\sql2k5;Database=master;Trusted_Connection=yes;"
SQLServer.Open()
Dim Command As New Data.OleDb.OleDbCommand
Command.Connection = SQLServer
Command.CommandText = "Create Database test123"
Command.ExecuteNonQuery()
Command.CommandText = "Use test123"
Command.ExecuteNonQuery()
Command.CommandText = "Create Table test (id int identity(1,1),test
nvarchar(50))"
Command.ExecuteNonQuery()

Command.CommandText = "CREATE LOGIN [primary\administrator] FROM
WINDOWS"
Command.ExecuteNonQuery()
Command.CommandText = "CREATE USER [PRIMARY\administrator] FOR
LOGIN [PRIMARY\administrator] WITH DEFAULT_SCHEMA=[dbo]"
Command.ExecuteNonQuery()
Command.CommandText = "Grant ALL to [primary\administrator]"
Command.ExecuteNonQuery()

Command.CommandText = "CREATE LOGIN [test] with Password='test'"
Command.ExecuteNonQuery()
Command.CommandText = "CREATE USER [test] FOR LOGIN [test] WITH
DEFAULT_SCHEMA=[guest]"
Command.ExecuteNonQuery()
Command.CommandText = "Grant SELECT to [test]"
Command.ExecuteNonQuery()

SQLServer.Close()

'SQL2k5 Username / Password:
Provider=SQLNCLI;Server=HostOrIP\Instance;Database=DatabaseName;Uid=myUsername;Pwd=myPassword;
'SQL2k5 Trusted Connection:
Provider=SQLNCLI;Server='HostOrIP;Database=DatabaseName;Trusted_Connection=yes;

'SQL2k5 Username / Password: Provider=sqloledb;Data Source=' SQL2k5
Username / Password: ;Initial Catalog=DatabaseName;User
Id=myUsername;Password=myPassword;
'SQL2k5 Trusted Connection: Provider=sqloledb;Data Source=' SQL2k5
Username / Password: ;Initial Catalog=DatabaseName;Integrated
Security=SSPI;

Creates a SQL2k5 Database named test123 on primary (instance sqlsk5).
creates a table (test) with 2 fields (id integer as identity and test as
nvarchar). creates 2 user (1 windows, 1 sql) and logins to the new
database. grant full access to the windows- and only-select access to
sql-user.

thats all.



tc said:
Does anyone have an example of how to connect to an SQL server and create
a new database, then add a user and assign permissions? One problem is
that users may be user authenticated, not Windows authenticated.

Thanks.
Click to expand...
Click to expand...
 
Back
Top