//Quote
It seems incredible to me that:
b) It would necessitate complex solutions like scripting the DB and/or
users, although I suppose worse things have happened. I really hope
there is a simpler solution.
//End Quote
Why is this "incredible " to you?
Having scripts for your database and database objects promotes repeatability
and success.
You can find a basic example here using sqlcmd.
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!583.entry
Whether or not you use sqlcmd or not, you ~should have your database
scripts....especially if you want to guarantee repeatable results on a
client installation.
..................
Below is some more TSQL code for giving certain domain users the role of
'db_datareader'.
It adds the login, the username to the database and grants the role.
Again, its accomplished via code. If you want repeatable results, then code
it up.
http://www.pragprog.com/the-pragmati.../extracts/tips
Don't Use Manual Procedures
A shell script or batch file will execute the same instructions, in the same
order, time after time.
Don't Live with Broken Windows
Fix bad designs, wrong decisions, and poor code when you see them.
Don't Use Wizard Code You Don't Understand
Wizards can generate reams of code. Make sure you understand all of it
before you incorporate it into your project.
Use MyDatabase-- ON MyServer
GO
declare @databaseName varchar(64)
select @databaseName = db_name()
declare @currentLoginName varchar(64)
print '/@databaseName/'
print @databaseName
print ''
select @currentLoginName = 'mydomain\myuser1'
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\myuser2'
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\myuser1'
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\myuser2'
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_datareader'
select @currentLoginName = 'mydomain\myuser2'
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\myuser1'
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Norman,
>
> Thanks for the response. Answers/comments in-line.
>
> On Sun, 30 Aug 2009 06:57:43 -0700, "Norman Yuan"
> <(E-Mail Removed)> wrote:
>
> After making some changes (no, I didn't log them yet). The error is:
>
> System.Data.SqlClient.SqlException: An error has occurred while
> establishing a connection to the server. When connecting to SQL
> Server 2005, this failure may be caused by the fact that under the
> default settings SQL Server does not allow remote connections.
> (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)
>
>>How the SQL Server Express is installed? That is, as regular instance
>>(default or named instance) or user instance via ClickOnce. It seems it is
>>former (if it is User Instance, you would not have that "login failed"
>>exception).
>
> Installed with Clickonce. I don't see any choices about instance, and
> assume it would be 'default'.
>
>>Obviously, then SQL server uses Windows integrated security. So, the use's
>>windows account must be mapped to a valid SQL Server login, which in turn
>>should be a user of targeting database with proper permissions.
>
> I also played around with using sa login but couldn't make it work.
>
>>If the SQL Server can be installed by "ClickOnce", it implies the user
>>must
>>have the right to do this (local admin/domain admin). If so, unless the
>>user
>>uses Vista, he/she should automatically has access to SQL Server.
>
> Test target machine is XP SP2. While this is my first effort, I just
> cannot figure out why it is not working the way you describe. I've
> come across one or two other posts where they also say the local
> (administrators group members) users get rights and can connect to SQL
> Express okay, although it seemed like they had the same problem as me
> initially and had to play around with it for a while to make it work
> (No details provided).
>
> SQL Server does (seem to) install okay. The error is generated by .NET
> as you can see.
>
> Could it be that I am developing with SQL Server 2005? SQL Server is
> running on the development machine. I tried another test with
> 'localhost' in the connection string ("Data Source=localhost;Initial
> Catalog=PB2be;Integrated Security=True") - same problem.
>
>>Or are you saying the "prerequisite" is actually done before "ClickOnce"?
>>In
>>this case, you should run some code (such as SQL script) to create needed
>>SQL Server login/database user.
>
> Well, it is a prerequisite in clickonce's list of prerequisites, and
> of course does get installed before the application file, but I don't
> think that is part of the problem, because after the fail, if I try to
> run setup again (SQL Express has already been installed at this point)
> even after a reboot, the same thing happens.
>
> Of course, if you use windows security, you
>>need to get user's windows account name during installation. If the user
>>is
>>in a network domain and you can expect all your users are in a given
>>domain
>>security group, then you can simply include the user group in the script.
>>Or
>>you can make the SQL Server uses SQL security and you can pre-create SQL
>>Server login and map it as needed database user. Then you app will always
>>log in as that SQL server login.
>
> This is a shareware application!
>
> It seems incredible to me that:
>
> a) this is not a VERY common scenario (SQL Express deployed with app
> via Clickonce to unknown target computers)
>
> b) It would necessitate complex solutions like scripting the DB and/or
> users, although I suppose worse things have happened. I really hope
> there is a simpler solution.
>
>
>>
>>"UsenetUser" <(E-Mail Removed)> wrote in message
>>news:(E-Mail Removed)...
>>> I'm working on a VB.NET shareware program so naturally I will have no
>>> knowledge of local users or credentials. It uses SQL Server for data
>>> storage. In development, SQL Server 2005. I publish it with clickonce,
>>> including SQL Express as a prerequiste, which installs ok on the
>>> client machine. My problem is, after the install, I get the error: The
>>> failed. Login failed for user 'Mycomputer\testuser'.
>>>
>>> How do I get around this?
>>>
>>> Thank you!
Similar Threads
