|
|
You're in the
sql server msde
group:Login permisions
sql server msde:
Hi!
I've just moved a mdb to MSDE without problem. The problem comes when I try to open the tables I have attached via ODBC. Tables are in MSDE and I call them from Access. I get an error telling me that the user is not allowed to read (error 18456). Following that message, I get a login screen. If I enter the sa user password it connects and allows me to see the table contents. I use MSDE Manager to manage the server, but any other option is welcomed if I should have more control over it. What I would like to do is that no password is asked when the users run the Access database. Thanks in advance. --
hi Xavier,
[quoted text, click to view] Xavier Boneu wrote: > Hi! > > I've just moved a mdb to MSDE without problem. The problem comes when > I try to open the tables I have attached via ODBC. Tables are in MSDE > and I call them from Access. > I get an error telling me that the user is not allowed to read (error > 18456). Following that message, I get a login screen. If I enter the > sa user password it connects and allows me to see the table contents. > > I use MSDE Manager to manage the server, but any other option is > welcomed if I should have more control over it. > > What I would like to do is that no password is asked when the users > run the Access database. > > Thanks in advance. you have then to specify, in the ODBC management console, to use integrated security... you have then to manage your Windows account to grant them access to the MSDE instance, using sp_grantlogin system stored procedure ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp ) and grant each login, or WinNT group (if you can manage them at group level instead of single account level you will gain some freedom) access to each desired database via sp_grantdbaccess system stored procedure ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_290z.asp ) -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
Hi!
Thanks for your info. I'll try it and post back the results. -- Xavier Boneu [quoted text, click to view] "Andrea Montanari" wrote:
> hi Xavier, > Xavier Boneu wrote: > > Hi! > > > > I've just moved a mdb to MSDE without problem. The problem comes when > > I try to open the tables I have attached via ODBC. Tables are in MSDE > > and I call them from Access. > > I get an error telling me that the user is not allowed to read (error > > 18456). Following that message, I get a login screen. If I enter the > > sa user password it connects and allows me to see the table contents. > > > > I use MSDE Manager to manage the server, but any other option is > > welcomed if I should have more control over it. > > > > What I would like to do is that no password is asked when the users > > run the Access database. > > > > Thanks in advance. > > you have then to specify, in the ODBC management console, to use integrated > security... you have then to manage your Windows account to grant them > access to the MSDE instance, using sp_grantlogin system stored procedure ( > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp ) > and grant each login, or WinNT group (if you can manage them at group level > instead of single account level you will gain some freedom) access to each > desired database via sp_grantdbaccess system stored procedure ( > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_290z.asp ) > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0 > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > interface) > --------- remove DMO to reply > >
Andrea,
I am trying what you suggest but it works only partialy. I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute a query from a client computer I get an error saying I have no permission to SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229) What I wrote was: EXEC sp_grantlogin 'companyname\username' EXEC sp_grantdbaccess 'companyname\username' but something is still missing... Could you give me a hand with this error? Thank you. Xavier -- Xavier Boneu [quoted text, click to view] "Andrea Montanari" wrote:
> hi Xavier, > Xavier Boneu wrote: > > Hi! > > > > I've just moved a mdb to MSDE without problem. The problem comes when > > I try to open the tables I have attached via ODBC. Tables are in MSDE > > and I call them from Access. > > I get an error telling me that the user is not allowed to read (error > > 18456). Following that message, I get a login screen. If I enter the > > sa user password it connects and allows me to see the table contents. > > > > I use MSDE Manager to manage the server, but any other option is > > welcomed if I should have more control over it. > > > > What I would like to do is that no password is asked when the users > > run the Access database. > > > > Thanks in advance. > > you have then to specify, in the ODBC management console, to use integrated > security... you have then to manage your Windows account to grant them > access to the MSDE instance, using sp_grantlogin system stored procedure ( > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp ) > and grant each login, or WinNT group (if you can manage them at group level > instead of single account level you will gain some freedom) access to each > desired database via sp_grantdbaccess system stored procedure ( > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_290z.asp ) > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0 > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > interface) > --------- remove DMO to reply > >
Andrea,
I am trying what you suggest but it works only partialy. I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute a query from a client computer I get an error saying I have no permission to SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229) What I wrote was: EXEC sp_grantlogin 'companyname\username' EXEC sp_grantdbaccess 'companyname\username' but something is still missing... Could you give me a hand with this error? Thank you. Xavier -- Xavier Boneu [quoted text, click to view] "Andrea Montanari" wrote:
> hi Xavier, > Xavier Boneu wrote: > > Hi! > > > > I've just moved a mdb to MSDE without problem. The problem comes when > > I try to open the tables I have attached via ODBC. Tables are in MSDE > > and I call them from Access. > > I get an error telling me that the user is not allowed to read (error > > 18456). Following that message, I get a login screen. If I enter the > > sa user password it connects and allows me to see the table contents. > > > > I use MSDE Manager to manage the server, but any other option is > > welcomed if I should have more control over it. > > > > What I would like to do is that no password is asked when the users > > run the Access database. > > > > Thanks in advance. > > you have then to specify, in the ODBC management console, to use integrated > security... you have then to manage your Windows account to grant them > access to the MSDE instance, using sp_grantlogin system stored procedure ( > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp ) > and grant each login, or WinNT group (if you can manage them at group level > instead of single account level you will gain some freedom) access to each > desired database via sp_grantdbaccess system stored procedure ( > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_290z.asp ) > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0 > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > interface) > --------- remove DMO to reply > >
Andrea,
Thanks for your help. By now, I have granted db_datareader and db_datawriter permisions to all the users. This way they can work and now, with more time, I will read the information you have suggested and 'study' to manage MSDE better. Thanks a lot!!! -- Xavier Boneu [quoted text, click to view] "Andrea Montanari" wrote:
> hi Xavier, > Xavier Boneu wrote: > > Andrea, > > > > I am trying what you suggest but it works only partialy. > > I've issued the sp_grantlogin and sp_grantdbaccess but when I try to > > execute a query from a client computer I get an error saying I have > > no permission to SELECT the object 'mytable', database 'mydtabase' , > > owner 'dbo'. (#229) > > the security goes on :D > as you add a database user to your preferred database, he/she will only be > member of the Public database role, and usually that role has no kind of > access to object's data... > you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the > Public database role (not recommended at all) or make him/her member of > specific builtin (or user defined) database role(s) [ > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_4boy.asp , > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_33s5.asp ] > ... > that's to say you can make him/her member of the default db_datareader role > to permit only data browsing, eventually db_datawriter to allow data > modification, or grant him/her only specific privilegs on specific tables... > usually you make him/her member of custom db roles granting only EXEC > privileges to defined stored procedures for data retrevial/modification, and > /or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not > a best practice to allow direct access to base table (please have a look at > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) > > you can start reading at > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx, > http://www.developer.com/tech/article.php/721441 > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0 > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > interface) > --------- remove DMO to reply > >
hi Xavier,
[quoted text, click to view] Xavier Boneu wrote: > Andrea, > > I am trying what you suggest but it works only partialy. > I've issued the sp_grantlogin and sp_grantdbaccess but when I try to > execute a query from a client computer I get an error saying I have > no permission to SELECT the object 'mytable', database 'mydtabase' , > owner 'dbo'. (#229) the security goes on :D as you add a database user to your preferred database, he/she will only be member of the Public database role, and usually that role has no kind of access to object's data... you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the Public database role (not recommended at all) or make him/her member of specific builtin (or user defined) database role(s) [ http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_4boy.asp , http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_33s5.asp ] ... that's to say you can make him/her member of the default db_datareader role to permit only data browsing, eventually db_datawriter to allow data modification, or grant him/her only specific privilegs on specific tables... usually you make him/her member of custom db roles granting only EXEC privileges to defined stored procedures for data retrevial/modification, and /or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not a best practice to allow direct access to base table (please have a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) you can start reading at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx, http://www.developer.com/tech/article.php/721441 -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |