SQL Server Logon

[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.

[pvdg42]

"tc" <tu**@idcodeware.co.ukwrote in message
news:OR**************@TK2MSFTNGP02.phx.gbl...

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.

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.

[Matthias Vastring]

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=Databas eName;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=m aster;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=Databas eName;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" <tu**@idcodeware.co.ukschrieb im Newsbeitrag
news:OR**************@TK2MSFTNGP02.phx.gbl...

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]

Many thanks, will start playing tomorrow!

"Matthias Vastring" <in**@vastring.dewrote in message
news:96**********************************@microsof t.com...

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=Databas eName;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=m aster;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=Databas eName;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" <tu**@idcodeware.co.ukschrieb im Newsbeitrag
news:OR**************@TK2MSFTNGP02.phx.gbl...

>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.