Vista - SQL Server connection string for deployment ?

VS 2005 clickonce deployment: What connection string do I use when I
deploy a windows forms application with an SQL Server DB? If I use sa
with no password, I get an error: login failed for user 'sa' Not
associated with a trusted SQL server connection. If I use integrated
security it fails, as I expect it would, because the user is unkown.

Thank you.

You need a mental overhaul if you think "sa" is a production-end-user
username (and password).
You NEVER give out the sa password. Only a handful of people should even
know what the sa password is.



First off, do you want to use sql authentication or windows integrated
authentication?

Second, bookmark www.connectionstrings.com

Here is some code based on windows authentication.

Look up each of the "sp_" calls if you want to learn more.
And google "sql vs windows authentication" "sql server"



This is Sql Server 2005 code, but will probably run on 2000,2008 as well.












declare @databaseName varchar(64)
select @databaseName = db_name()
declare @currentLoginName varchar(64)

print '/@databaseName/'
print @databaseName
print ''


select @currentLoginName = 'mydomain\user1'

if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end


select @currentLoginName = 'mydomain\user2'

if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end


select @currentLoginName = 'mydomain\user3'

if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end



select @currentLoginName = 'mydomain\user4'

if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end


select @currentLoginName = 'mydomain\user5'

if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end



select @currentLoginName = 'mydomain\user6'

if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end


--==========================









declare @sp_grantdbaccess_return_value int
declare @sp_grantdbaccess_return_value_total int
select @sp_grantdbaccess_return_value_total = 0

-- declare @currentLoginName varchar(64)



select @currentLoginName = 'mydomain\user1'
if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
6382 )
begin
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
select @sp_grantdbaccess_return_value_total =
@sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end

select @currentLoginName = 'mydomain\user2'
if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
6382 )
begin
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
select @sp_grantdbaccess_return_value_total =
@sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end

select @currentLoginName = 'mydomain\user3'
if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
6382 )
begin
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
select @sp_grantdbaccess_return_value_total =
@sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end


select @currentLoginName = 'mydomain\user4'
if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
6382 )
begin
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
select @sp_grantdbaccess_return_value_total =
@sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end

select @currentLoginName = 'mydomain\user5'
if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
6382 )
begin
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
select @sp_grantdbaccess_return_value_total =
@sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end




print '/@sp_grantdbaccess_return_value_total/'
print @sp_grantdbaccess_return_value_total








-- declare @currentLoginName varchar(64)

declare @sp_addrolemember_return_value int
declare @sp_addrolemember_return_value_total int
select @sp_addrolemember_return_value_total = 0


declare @currentRole varchar(64)
--select @currentRole = 'db_owner'
select @currentRole = 'db_datareader'




select @currentLoginName = 'mydomain\user2'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end


select @currentLoginName = 'mydomain\user3'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end

select @currentLoginName = 'mydomain\user4'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end

select @currentLoginName = 'mydomain\user5'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end

select @currentLoginName = 'mydomain\user6'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end

select @currentLoginName = 'mydomain\user1'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end


print '/@sp_addrolemember_return_value_total/'
print @sp_addrolemember_return_value_total









"UsenetUser" <spam@xxxxxx> wrote in message
news:inrn75luet8hdbesjml65b2bm7gk86dpmc@xxxxxx