|
security
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Returning current user from SQLServerI have converted my Access 2007 back end to a SQLServer and am trying to
return the current user back to the application. Unfortunately if I use the built in function CurrentUser is only returns Admin which is from Access and if I try to use the TransactSQL function CURRENT_USER in a SQL Docmd.RunSQL statement (Select CURRENT_USER) Access errors and says it is looking for a SELECT, INSERT etc type of statement. If I run 'Select CURRENT_USER Go' in a SQL Query analyser window it returns the logged in user name. Does anyone know how to get Access 2007 to tell me the name of the user that is logged onto the backend (SQLServer) database? Thanks I don't believe it's possible to use RunSQL for T/SQL statements. Access has
no way of knowing that you're trying to connect to an external data source. As well, as you've already discovered, RunSQL can only be used with Action queries (INSERT INTO, UPDATE, DELETE) You'll have to write a pass-through query, then open a recordset based on that pass-through query to retrieve its response. -- Show quoteHide quoteDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Robman" <Rob***@discussions.microsoft.com> wrote in message news:E4885F0A-0FD3-440F-B378-8057AEFA5D35@microsoft.com... >I have converted my Access 2007 back end to a SQLServer and am trying to > return the current user back to the application. Unfortunately if I use > the > built in function CurrentUser is only returns Admin which is from Access > and > if I try to use the TransactSQL function CURRENT_USER in a SQL > Docmd.RunSQL > statement (Select CURRENT_USER) Access errors and says it is looking for a > SELECT, INSERT etc type of statement. If I run 'Select CURRENT_USER Go' > in a > SQL Query analyser window it returns the logged in user name. > Does anyone know how to get Access 2007 to tell me the name of the user > that > is logged onto the backend (SQLServer) database? > Thanks > Douglas:
Thanks very much for the advice! It seems very strange that MS has removed User level security from Access but has provided no easy way to find out who the current user is when working on SQL Backends! As you can imagine without user level security it is relatively difficult to secure individual things in a database. Anyway I will try to figure out how to do this but so far it has me stumped and I don't know if a pass through query will do the job as there is only one value to be returned in each case! Thanks a lot! Robman Show quoteHide quote "Douglas J. Steele" wrote: > I don't believe it's possible to use RunSQL for T/SQL statements. Access has > no way of knowing that you're trying to connect to an external data source. > As well, as you've already discovered, RunSQL can only be used with Action > queries (INSERT INTO, UPDATE, DELETE) > > You'll have to write a pass-through query, then open a recordset based on > that pass-through query to retrieve its response. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Robman" <Rob***@discussions.microsoft.com> wrote in message > news:E4885F0A-0FD3-440F-B378-8057AEFA5D35@microsoft.com... > >I have converted my Access 2007 back end to a SQLServer and am trying to > > return the current user back to the application. Unfortunately if I use > > the > > built in function CurrentUser is only returns Admin which is from Access > > and > > if I try to use the TransactSQL function CURRENT_USER in a SQL > > Docmd.RunSQL > > statement (Select CURRENT_USER) Access errors and says it is looking for a > > SELECT, INSERT etc type of statement. If I run 'Select CURRENT_USER Go' > > in a > > SQL Query analyser window it returns the logged in user name. > > Does anyone know how to get Access 2007 to tell me the name of the user > > that > > is logged onto the backend (SQLServer) database? > > Thanks > > > > > Where did you get your misinformation? Microsoft hasn't removed user level
security. It's still available for all db files that use the Jet db engine. If you're complaining you don't have user level security, you're not using Jet. The accdb files don't use Jet, they use the ACE db engine. Chris Microsoft MVP Robman wrote: >It seems very strange that MS has removed User level security from Access -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-security/200810/1 Chris the information I have came straight out of the MS Access help files
for 2007 "Cause User-level security is not supported by the new file formats in Microsoft Office Access 2007." Hence the reason I am trying to get the user name from the SQLServer back end. Show quoteHide quote "Chris O'C via AccessMonster.com" wrote: > Where did you get your misinformation? Microsoft hasn't removed user level > security. It's still available for all db files that use the Jet db engine. > If you're complaining you don't have user level security, you're not using > Jet. The accdb files don't use Jet, they use the ACE db engine. > > Chris > Microsoft MVP > > > Robman wrote: > > >It seems very strange that MS has removed User level security from Access > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-security/200810/1 > > That tells me if I need user level security not to save the db in the new
file format, accdb. Use mdbs, which are managed by Jet. Jet is *required* for user level security. Did you interpret it differently? Did you assume Access 2007 isn't capable of creating mdbs? It can make mdbs, adps and accdbs and all the derivitive file types like mdes, accdts, etc. You're not forced to use the accdb file format to store your db app. Convert your front end db file to an Access 2007 mdb, apply user level security and use CurrentUser to get the user's name. Chris Microsoft MVP Robman wrote: >Chris the information I have came straight out of the MS Access help files >for 2007 > >"Cause >User-level security is not supported by the new file formats in Microsoft >Office Access 2007." > >Hence the reason I am trying to get the user name from the SQLServer back end. Unfortunately that is not what I read the message to mean. My understanding
was that if you wanted to use the new capabilities of Access 2007 then you had to save your db in the new format - accdb as opposed to the old mdb. Thanks for your post. Show quoteHide quote "Chris O'C via AccessMonster.com" wrote: > That tells me if I need user level security not to save the db in the new > file format, accdb. Use mdbs, which are managed by Jet. Jet is *required* > for user level security. > > Did you interpret it differently? Did you assume Access 2007 isn't capable > of creating mdbs? It can make mdbs, adps and accdbs and all the derivitive > file types like mdes, accdts, etc. You're not forced to use the accdb file > format to store your db app. > > Convert your front end db file to an Access 2007 mdb, apply user level > security and use CurrentUser to get the user's name. > > Chris > Microsoft MVP > > > Robman wrote: > >Chris the information I have came straight out of the MS Access help files > >for 2007 > > > >"Cause > >User-level security is not supported by the new file formats in Microsoft > >Office Access 2007." > > > >Hence the reason I am trying to get the user name from the SQLServer back end. > > -- > Message posted via http://www.accessmonster.com > > Robman wrote:
> Unfortunately that is not what I read the message to mean. My Some of the new features do require that.> understanding was that if you wanted to use the new capabilities of > Access 2007 then you had to save your db in the new format - accdb as > opposed to the old mdb. Thanks for your post. Implementing user level security just so you can capture user's names would be phenominal overkill. Just use the API call to return the current windows account name. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com He was also interested in securing "individual things in a database".
Chris Microsoft MVP Rick Brandt wrote: Show quoteHide quote >Implementing user level security just so you can capture user's names would >be phenominal overkill. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-security/200810/1 Chris O'C via AccessMonster.com wrote:
>> Rick Brandt wrote: With a SS back end data should be secured with SS security. If he wants to > >> Implementing user level security just so you can capture user's >> names would be phenominal overkill. > He was also interested in securing "individual things in a database". > use code to decide what users can do in the front end getting the user name from SS or the Windows API is still a better option than going through all the guff to set up Access ULS particularly if he wants to use the new AccDB features. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Rick (and Chris):
Thanks very much for your posts on this subject. Just to clarify: I have secured the data in SS using OS based security and roles. I am attempting to create some "smart" menus that display only what is appropriate for the user signed in. I have figured out how to use a Pass Through query to get the user name back - this comes with some problems because SYSTEM-USER returns both the username and the PC name i.e. PCNAME/Username instead of just user name and to be honest I can't remember how to use the StringLeft,Mid,Right funcitons to get rid of the PC name and the slash (/) SO having said that is there a way within Access to "call" the API on the server to get just the user name that is logged onto the Access session? Not being a "programmer" it takes a while to learn each of these things one by one so I really appreciate all the help that both of your are providing! Thanks Robman Show quoteHide quote "Rick Brandt" wrote: > Chris O'C via AccessMonster.com wrote: > >> Rick Brandt wrote: > > > >> Implementing user level security just so you can capture user's > >> names would be phenominal overkill. > > He was also interested in securing "individual things in a database". > > > > With a SS back end data should be secured with SS security. If he wants to > use code to decide what users can do in the front end getting the user name > from SS or the Windows API is still a better option than going through all > the guff to set up Access ULS particularly if he wants to use the new AccDB > features. > > -- > Rick Brandt, Microsoft Access MVP > Email (as appropriate) to... > RBrandt at Hunter dot com > > > > You can find the code here:
http://www.mvps.org/access/api/api0008.htm Use the fOSUserName function to get the current user's login name. Chris Microsoft MVP Robman wrote: Show quoteHide quote >is there a way >within Access to "call" the API on the server to get just the user name that >is logged onto the Access session? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-security/200810/1 It worked just fine - thank you!
The only problem I have run into is how to make the username variable "global" or "public" so that it persists throughout the session. I know that each time I want to user the user name I could call the function again but what I tried to do, unsuccesfully, was to declare the variable as public so that it would be available at all times (once read). The way I attempted to do this was in a declaration: Public loggeduser as string loggeduser=fOSUserName Even though I declared loggeduser as a variable length string I received an error code "Invalid attribute in Sub or Function" and the "Public" was highlighted. A thorough re-reading of the Public function did not clarify why the statement was invalid so hopefully somebody knows! Perhaps there is a better way to accomplish what I want to do other than try and make loggeduser a persistent variable? Thanks for all your help so far! Robman Show quoteHide quote "Chris O'C via AccessMonster.com" wrote: > You can find the code here: > > http://www.mvps.org/access/api/api0008.htm > > Use the fOSUserName function to get the current user's login name. > > Chris > Microsoft MVP > > > Robman wrote: > > >is there a way > >within Access to "call" the API on the server to get just the user name that > >is logged onto the Access session? > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-security/200810/1 > > Because it's a variable declaration, you can put it in the declarations
section of the module: Public loggeduser as string Because it's a command, you must put this line inside a procedure: loggeduser=fOSUserName Unless you make an mde, as soon as there's an error, even if it's handled by your error handler, all global variables will be reset, so loggeduser = "". A better way is to open a hidden form on startup that has an unbound text box. When this hidden form opens, it assigns the user's name to the text box, like this: Me.txtUserName = fOSUserName Later whenever you need the user's name, use vba code to get it from the text box in the hidden form: Me.txtUpdatedBy = Forms!frmHidden.txtUserName Chris Microsoft MVP Robman wrote: Show quoteHide quote >It worked just fine - thank you! >The only problem I have run into is how to make the username variable >"global" or "public" so that it persists throughout the session. >I know that each time I want to user the user name I could call the function >again but what I tried to do, unsuccesfully, was to declare the variable as >public so that it would be available at all times (once read). > >The way I attempted to do this was in a declaration: >Public loggeduser as string >loggeduser=fOSUserName > >Even though I declared loggeduser as a variable length string I received an >error code "Invalid attribute in Sub or Function" and the "Public" was >highlighted. >A thorough re-reading of the Public function did not clarify why the >statement was invalid so hopefully somebody knows! > >Perhaps there is a better way to accomplish what I want to do other than try >and make loggeduser a persistent variable? Chris:
No doubt two heads are better than one! Thanks for the suggestion - that should get the job done - what I will do is since I have put the user name on the opening form I will just leave that form open behind everything else and reference it! Thanks for all your help! Show quoteHide quote "Robman" wrote: > It worked just fine - thank you! > The only problem I have run into is how to make the username variable > "global" or "public" so that it persists throughout the session. > I know that each time I want to user the user name I could call the function > again but what I tried to do, unsuccesfully, was to declare the variable as > public so that it would be available at all times (once read). > > The way I attempted to do this was in a declaration: > Public loggeduser as string > loggeduser=fOSUserName > > Even though I declared loggeduser as a variable length string I received an > error code "Invalid attribute in Sub or Function" and the "Public" was > highlighted. > A thorough re-reading of the Public function did not clarify why the > statement was invalid so hopefully somebody knows! > > Perhaps there is a better way to accomplish what I want to do other than try > and make loggeduser a persistent variable? > > Thanks for all your help so far! > Robman > > "Chris O'C via AccessMonster.com" wrote: > > > You can find the code here: > > > > http://www.mvps.org/access/api/api0008.htm > > > > Use the fOSUserName function to get the current user's login name. > > > > Chris > > Microsoft MVP > > > > > > Robman wrote: > > > > >is there a way > > >within Access to "call" the API on the server to get just the user name that > > >is logged onto the Access session? > > > > -- > > Message posted via AccessMonster.com > > http://www.accessmonster.com/Uwe/Forums.aspx/access-security/200810/1 > > > > |
|||||||||||||||||||||||
Other interesting topics