How to connect to a custom database

  • Thread starter Thread starter Rinaldo
  • Start date Start date
R

Rinaldo

Hi,

I've a question concerning databases. Readed the book beginning c# databases
2008 i want to connect to my custom database.

I have the following thing done. Copy my database MyData.mdf to C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

The following connectstring : string connString = @"server =
..\sqlexpress;integrated security=true; initial catalog = MyData.mdf";
SqlConnection conn = new SqlConnection(connString);

but when I try to acces mydata.mdf I get the message: Cannot open database
requested by the login. The login failed. Login failed for user
............... MyName

What I doing wrong?

Rinaldo
 
Mark Rae said:
1) Have you configured SQL Server Express for integrated security?

2) Have you set up your Windows user as a user in the database?
Click to expand...

Hi Mark,

If I connect to the northwind database there is no problem, so integrated
security must be good, otherwise how do I set that..

I always login as windows user in the sqlserver if I started it up, no
username or passwords.

The database is made by the integrated database wizard in VC#.

Rinaldo
 
First off, you ~always need to know which "who" (account) a program is
running under.

Second, you need to security-enable a database for that person.


For the first thing, here is some crappy/crappy code to help you identify
the "who".
private string FindIIdentity()
{

try
{
//'Dim user As WindowsPrincipal =
CType(System.Threading.Thread.CurrentPrincipal, WindowsPrincipal)
//'Dim ident As IIdentity = user.Identity

string returnValue = string.Empty;


WindowsIdentity ident = WindowsIdentity.GetCurrent();
returnValue = ident.Name;

try
{
returnValue += " on " + System.Environment.MachineName;
}
catch (Exception ex)
{
Conditionals.Trace.WriteMessage(ex.Message);
}

return returnValue;
}


catch (Exception ex)
{
Conditionals.Trace.WriteMessage(ex.Message);
return "Error Finding Identity";
}

}




Second, below is some TSQL code that will grant access to a certain user.


--example code -- select @currentLoginName = 'mydomain\user1'
--example code (for asp.net apps and for future googlers out there who find
this article -- select @currentLoginName = 'COMPUTER_NAME\ASPNET' --where
COMPUTER_NAME is the server that is hosting your asp.net application











Below is the TSQL code to grant access.

If you want to learn more, than look in BOL (books online for Sql Server)
about all the procedures that start with
sp_

The will run correctly AFTER you replace the values
'mydomain\user1'
'mydomain\user2'
'mydomain\user3'
'mydomain\user4'
'mydomain\user5'

with real domain\userNames




--------------START TSQL CODE

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
 
Hi MArk,

Thanks for the reply.

Now I have connected to my custom database. Did it another way, made again a
database in VS with a connectionstring made into it.

Rinaldo
 
Back
Top