RO tables in SQLServer from Access

Hi all
I used the import utility to move some access tables to a SQL Svr db, and
while it all seemed to go AOK, whatever I do the resultant SQL tables appear
to be read-only. There are other tables in the SQL db that will read & write
OK.
Can any one help and tell me how to make the SQL tables writable'
thanks
JimboJim
Does the user who operates with a table have an apropriate permissions?
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:e0o7na$jib$1$8302bc10@.news.demon.co.uk...
> Hi all
> I used the import utility to move some access tables to a SQL Svr db, and
> while it all seemed to go AOK, whatever I do the resultant SQL tables
> appear
> to be read-only. There are other tables in the SQL db that will read &
> write
> OK.
> Can any one help and tell me how to make the SQL tables writable'
> thanks
> Jimbo
>
>|||Each table must have a unique column(s) (e.g. primary key) in order to be
updatable by database APIs. The SQL generated by the API includes the key
columns in the WHERE clause to ensure only one row is updated/deleted. This
is one reason why all tables should have a primary key.
IIRC, Access prompts you to specify the unique column(s) when a SQL Server
table is linked. This defaults to the primary key, if one exists.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:e0o7na$jib$1$8302bc10@.news.demon.co.uk...
> Hi all
> I used the import utility to move some access tables to a SQL Svr db, and
> while it all seemed to go AOK, whatever I do the resultant SQL tables
> appear
> to be read-only. There are other tables in the SQL db that will read &
> write
> OK.
> Can any one help and tell me how to make the SQL tables writable'
> thanks
> Jimbo
>
>

Ristricted User Permissions on Services

Hi!
I have a user with restricted permissions on his account but still he
wants to start and stop SQL server services for MSDE (runtime SQL
server) installed on his computer. So whenever he tries to start or
stop the service he gets a message "Access Denied", so can anyone guide
me how can I enable atleast this feature on his machine still using his
restricted account.
cheers!!!
You should have admin rights to start or stop services.
Regards
Amsh
sql

Ristricted User Permissions on Services

Hi!
I have a user with restricted permissions on his account but still he
wants to start and stop SQL server services for MSDE (runtime SQL
server) installed on his computer. So whenever he tries to start or
stop the service he gets a message "Access Denied", so can anyone guide
me how can I enable atleast this feature on his machine still using his
restricted account.
cheers!!!You should have admin rights to start or stop services.
Regards
Amsh

Rights needed to restore DB

I have an SQL 2005 box in an academic setting. Students log-in using SQL
server authentication. They can create, modify and backup their DBs'
When using the restore wizard and specifying a file location where the
backup is located they get an error.
c:\ProgramFiles\...\...\...\Backup
Cannot access the specified path or file on the server. Verify that you have
the necessary security privileges and that the path or file exists
If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate dialog box.
The back-ups are being created and can be restored by the Instructor (with
full system rights)
What am I missing here? students can write but not read there own files.
Any help is appreciated.
Todd>I have an SQL 2005 box in an academic setting. Students log-in using SQL
> server authentication. They can create, modify and backup their DBs'
> When using the restore wizard and specifying a file location where the
> backup is located they get an error.
> c:\ProgramFiles\...\...\...\Backup
> Cannot access the specified path or file on the server. Verify that you
> have
> the necessary security privileges and that the path or file exists
> If you know that the service account can access a specific file, type in
> the
> full path for the file in the File Name control in the Locate dialog box.
> The back-ups are being created and can be restored by the Instructor (with
> full system rights)
> What am I missing here? students can write but not read there own files.
BACKUP DATABASE permission is not enough for restoring. If the database
being restored does not exist, the user must have CREATE DATABASE
permissions to be able to execute RESTORE. If the database exists, RESTORE
permissions default to members of the sysadmin and dbcreator fixed server
roles and the owner (dbo) of the database.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||The users are a part of the dbcreator fixed role. Do they need spcific right
s
to the folder where the backups are stored or does a sytem account manage
that piece.
If so which account?
"Dejan Sarka" wrote:

> BACKUP DATABASE permission is not enough for restoring. If the database
> being restored does not exist, the user must have CREATE DATABASE
> permissions to be able to execute RESTORE. If the database exists, RESTORE
> permissions default to members of the sysadmin and dbcreator fixed server
> roles and the owner (dbo) of the database.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||> The users are a part of the dbcreator fixed role. Do they need spcific
> rights
> to the folder where the backups are stored or does a sytem account manage
> that piece.
> If so which account?
If you are using T-SQL Restore command from SSMS, then it is SQL Server
account. Check whether it can reach the folder. For example, you could use a
mapped drive, and the SQL Server account does not know anything about the
mapping you do under your user account. In addition, LocalSystem account, if
your server uses it, can not go beyond local computer - it can not reach
shared folders on the network. Try to login with servers account and read
the backup files.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Dejan,
We are using the backup/ restore wizard and trying to reach the default
SQL back-up folder on the local drive. The Administrator and Instructor
accounts can reach the folder and restore a file, but they have sysadmin
role. Students with dbcreator role can backup but not restore.
The SQLUser account in widows has full control rights to the folder.
"Dejan Sarka" wrote:

> If you are using T-SQL Restore command from SSMS, then it is SQL Server
> account. Check whether it can reach the folder. For example, you could use
a
> mapped drive, and the SQL Server account does not know anything about the
> mapping you do under your user account. In addition, LocalSystem account,
if
> your server uses it, can not go beyond local computer - it can not reach
> shared folders on the network. Try to login with servers account and read
> the backup files.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||> We are using the backup/ restore wizard and trying to reach the default
> SQL back-up folder on the local drive. The Administrator and Instructor
> accounts can reach the folder and restore a file, but they have sysadmin
> role. Students with dbcreator role can backup but not restore.
So, what is the exact error message?
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Here's the message:
"C:\Program Files\Microsoft SQL Server\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have
the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate Dialog box."
The File Name control is greyed out and doesn't allow students to type. Also
trying to Add a file path fails.
"Dejan Sarka" wrote:

> So, what is the exact error message?
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||Have you tried to login with the service account and access the path? I know
it does not sound logical that SQL Server would not have access to its own
path, but anway...
In addition, could you script your student login / user / permissions, in
order I can check it?
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:78358925-255D-4EB7-B5D5-8448256078E3@.microsoft.com...[vbcol=seagreen]
> Here's the message:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Backup
> Cannot access the specified path or file on the server. Verify that you
> have
> the necessary security privileges and that the path or file exists.
> If you know that the service account can access a specific file, type in
> the
> full path for the file in the File Name control in the Locate Dialog box."
> The File Name control is greyed out and doesn't allow students to type.
> Also
> trying to Add a file path fails.
>
> "Dejan Sarka" wrote:
>

rights for each login

Hi ,
1 - How do i know the rights for each logins ? is there
any command to show it ?
2 - There's a client application linked to the SQL
server , how will i be able to check if it's using the
application role or the role specified inside the SQL
server ?
Appreciate if there's anyone that could help me out
thks & rdgs
Hi
1) Look at sp_helprotect system procedure.
2)sp_helprole
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:9ec501c4791f$01827350$a401280a@.phx.gbl...
> Hi ,
> 1 - How do i know the rights for each logins ? is there
> any command to show it ?
> 2 - There's a client application linked to the SQL
> server , how will i be able to check if it's using the
> application role or the role specified inside the SQL
> server ?
> Appreciate if there's anyone that could help me out
> thks & rdgs
>
|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there
>
>.
>
|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there
>
>.
>
|||BOL says
B. List the permissions for a user
This example lists all permissions that user Judy has in the current
database.
EXEC sp_helprotect NULL, 'Judy'
If you get the error (in your case) so the user abc does not exist in the
system"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:a21f01c4793b$87054f40$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Uri ,
> thks but can i find out further
> i have used exec sp_helprotect null, 'abc' which abc is a
> user. however the system returns 'there are no matching
> roles on which to report'
> in ur opinon , wat could be the cause ?
> thks & rdgs
> message
> there
|||Hi
I have actually looked under the users of that database
before i execute the command. it's ok for another server
but not this one
rdgs
>--Original Message--
>BOL says
>B. List the permissions for a user
>This example lists all permissions that user Judy has in
the current
>database.
>EXEC sp_helprotect NULL, 'Judy'
>If you get the error (in your case) so the user abc does
not exist in the
>system"maxzsim" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:a21f01c4793b$87054f40$a501280a@.phx.gbl...
a[vbcol=seagreen]
in[vbcol=seagreen]
the
>
>.
>

rights for each login

Hi ,
1 - How do i know the rights for each logins ? is there
any command to show it ?
2 - There's a client application linked to the SQL
server , how will i be able to check if it's using the
application role or the role specified inside the SQL
server ?
Appreciate if there's anyone that could help me out
thks & rdgsHi
1) Look at sp_helprotect system procedure.
2)sp_helprole
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:9ec501c4791f$01827350$a401280a@.phx.gbl...
> Hi ,
> 1 - How do i know the rights for each logins ? is there
> any command to show it ?
> 2 - There's a client application linked to the SQL
> server , how will i be able to check if it's using the
> application role or the role specified inside the SQL
> server ?
> Appreciate if there's anyone that could help me out
> thks & rdgs
>|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there[vbcol=seagreen]
>
>.
>|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there[vbcol=seagreen]
>
>.
>|||BOL says
B. List the permissions for a user
This example lists all permissions that user Judy has in the current
database.
EXEC sp_helprotect NULL, 'Judy'
If you get the error (in your case) so the user abc does not exist in the
system"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:a21f01c4793b$87054f40$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Uri ,
> thks but can i find out further
> i have used exec sp_helprotect null, 'abc' which abc is a
> user. however the system returns 'there are no matching
> roles on which to report'
> in ur opinon , wat could be the cause ?
> thks & rdgs
> message
> there|||Hi
I have actually looked under the users of that database
before i execute the command. it's ok for another server
but not this one
rdgs
>--Original Message--
>BOL says
>B. List the permissions for a user
>This example lists all permissions that user Judy has in
the current
>database.
>EXEC sp_helprotect NULL, 'Judy'
>If you get the error (in your case) so the user abc does
not exist in the
>system"maxzsim" <anonymous@.discussions.microsoft.com>
wrote in message
>news:a21f01c4793b$87054f40$a501280a@.phx.gbl...
a[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>

Returning the Primary Key

Is there a way to get asp.net to return the primary key in SQLServer after INSERT as I need to re-name an uploaded file with the primary key. Thanks.Do mean the IDENTITY cause otherwise you must have inserted the primary key yourself!?|||Yeah I mean the Identity.|||Hi nova,

if you are using a stored procedure to insert then

Function()
Delcare con,
Delcare command,adapters etc....
'declare a param
Dim parameter1 As New SqlParameter("@.ID", SqlDbType.Int)
parameter1.Direction = ParameterDirection.Output

'after the executescalar

Dim iID As Integer = yourcommand.Parameters("@.ID").Value.ToString()
Return iID
End Function

Hope this helps?
m_7e7|||Multiple ways to get Identity value from SQL:

System variable @.@.Identity, this is database scope

Function SCOPE_IDENTITY(), Or IDENT_CURRENT('Table name')|||I might have to do it the srored procedure way, however at the moment i'm just using a function that executes the sql variable when it's called e.g

Dim queryString As String = "INSERT INTO [tblKnowledgeBase] ([knowledgeTitle], [knowledgeType], [knowledgeCust"& _
"omerView], [knowledgeProjectID], [knowledgeDesc], [knowledgeAttachName], [knowle"& _
"dgeAttachType], [knowledgeAttachSize], [knowledgeAdditionUserID], [knowledgeAddi"& _
"tionDate], [KnowledgeApproved]) VALUES (@.knowledgeTitle, @.knowledgeType, @.knowle"& _
"dgeCustomerView, @.knowledgeProjectID, @.knowledgeDesc, @.knowledgeAttachName, @.kno"& _
"wledgeAttachType, @.knowledgeAttachSize, @.knowledgeAdditionUserID, @.knowledgeAddi"& _
"tionDate, @.KnowledgeApproved)"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
Dim rowsAffected As Integer = 0
 dbConnection.Open
 Try
 rowsAffected = dbCommand.ExecuteNonQuery
 Finally
 dbConnection.Close
 End Try
 Return rowsAffected

Is there a way I can retrieve it with this method of insertion, If I use stored procedures I will have compatibility problems when creating a microsoft Access/mySQL version.|||Is your ID(Primary Key) a AutoNumber ??|||My advice would be to bite the bullet and use a stored proc.|||Try this query instead.............

Dim queryString As String = "INSERT INTO [tblKnowledgeBase] ([knowledgeTitle], [knowledgeType], [knowledgeCust"& _

"omerView], [knowledgeProjectID], [knowledgeDesc], [knowledgeAttachName], [knowle"& _

"dgeAttachType], [knowledgeAttachSize], [knowledgeAdditionUserID], [knowledgeAddi"& _

"tionDate], [KnowledgeApproved]) VALUES (@.knowledgeTitle, @.knowledgeType, @.knowle"& _

"dgeCustomerView, @.knowledgeProjectID, @.knowledgeDesc, @.knowledgeAttachName, @.kno"& _

"wledgeAttachType, @.knowledgeAttachSize, @.knowledgeAdditionUserID, @.knowledgeAddi"& _

"tionDate, @.KnowledgeApproved)";"SELECT @.@.IDENTITY AS Ident";

Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

dbCommand.CommandText = queryString

dbCommand.Connection = dbConnection

Dim rowsAffected As Integer = 0

dbConnection.Open

Try

rowsAffected = dbCommand.Executescalar

Finally

dbConnection.Close

End Try

Return rowsAffected

*********Note*************
Use an executescalar instead of execnonquery and try.....|||Yeah it is an utonumber|||hey you got it???