additional SQL users

Currently the only ODBC account available is SA. How would I set up an
additional account for use of the ODBC driver with limited access?

0
7/26/2006 4:11:30 PM
greatplains 29623 articles. 2 followers. Follow

3 Replies
146 Views

Similar Articles

[PageSpeed] 55

Create a new SQL Login in Enterprise Manager and grant that login
access to the databases to which you want it to connect.  You can
restrict access a number of ways (i.e. db_denydatawriter database role)
such as creating your own database roles and assigning your own
permissions to objects or granting specific access only to specific
objects for a specific database user.

0
michaelj2 (180)
7/26/2006 5:25:58 PM
I have been cautioned not to continue down this path since it causes
database instability and performance inefficiencies. My consultant has
suggested using Smart Builder to replace my existing SQL updates direct
to Excel reports. Any thoughts? Is the method you described of granting
access only to specific objects per user a common approach or "custom"
work?

Thanks for your reply by the way.


MichaelJ wrote:
> Create a new SQL Login in Enterprise Manager and grant that login
> access to the databases to which you want it to connect.  You can
> restrict access a number of ways (i.e. db_denydatawriter database role)
> such as creating your own database roles and assigning your own
> permissions to objects or granting specific access only to specific
> objects for a specific database user.

0
7/26/2006 9:39:10 PM
My personal preference is to write my own queries using Stored Procs,
Views, and Functions.  I prefer not to be constrained by a tool or
outlay cash for one I can work more efficiently and effectively
without.  I replicate data to a separate SQL Server to keep my
reporting environment separate from my transactional processing
environment but the only database instability would be self-inflicted
due to inefficient or poorly tested code introduced to a production
environment.

If you don't have the technical experience or knowledge of the GP table
structure required to do this safely then a 3rd party tool might be
right for you.  You can always setup a test environment in which to
develop and test your queries before deploying them to production to
minimize database instability and performance inefficiencies.  I can't
say I've used SmartList Builder but writing your own queries is much
more efficient than using SmartList to mine your data which I have
found in some instances to be a significant performance bottleneck.

We create our own logins, DB roles, and grant scripts to grant security
to specific objects by DB role.  You can modify the Grant70.sql pretty
easily to grant different permissions to different objects by DB Role.
It's secure, stable, and performance isn't an issue as a result of this
practice.

0
michaelj2 (180)
7/26/2006 9:59:47 PM
Reply:

Similar Artilces:

Adding users to distribution list
I am looking to add about 4000 AD users to a DL. What is the best way to script this (or csvde)? Thanks! If there is something uniquely identifiable about these users (such as OU, Department name, City, etc...), I would create a Dynamic Distribution List instead. You could always select these users in an OU or by using the Find utility in ADUC, select them, then right click and add them to the group. Unless these users are spread out all over your AD, that might be the best way to add them to a group. Not very creative, but maybe someone will think of something better. :-) -- Ji...

Disabled user takes 180mins to become active after being enabled
When we enable a disabled user account it can tae 180mins before the mailbox is accessable again, how can I speed this up? Thanks S Which OS? If you have more DC's you might want to replicate DC's manually. Replication depends on AD setup and configuration. Kind regards, Menko den Ouden NL "SW" <SW@discussions.microsoft.com> wrote in message news:8CD8D434-723A-40D3-A3B7-9B63E4A34CEF@microsoft.com... > When we enable a disabled user account it can tae 180mins before the > mailbox > is accessable again, how can I speed this up? > > Thanks > &g...

Pass Through to SQL Server Optimization Questions II
This is a test in running a pass through to SQL Server query vs the same query run to linked SQL Server tables. The pass through query runs much faster but loops through the recordset much slower. What accounts for this? The results of my test (Function DoTimer below) are here. I realize that the results gleaned from the recordset loop could be done in its own query, but the point of this is just to test. 5. q_PassThrough: 3.75 Total Time: 19.3125 Rows: 58532 - SalesAllTotal: 8121654735.25297 Rows: 58532 - Looping through recordset cumulative total time: 4.859375 seconds. q_PassThrough - A...

Worksheet Column Widths Different For Different Users
There are 2 of us that use the same Excel Workbook. We are all using Excel 2003. When I check the widths of the columns in the Worksheet, they are different then when the others check them. For example, for me, one column is set at a width of 10.38. That same column, shows a width of 10.43 for the other user. When I try to change the width of that column to 10.43, it stays at 10.38 (for me). I can't even change it to 10.43. When the other user tries to change that column to 10.38, it stays at 10.43 (for that user). So, that user can't even change it to 10.38. ...

Contracts additional Features
First suggestion is to allow decimal points in the Quantity field on the contract line. Second Feature, provide a mechanism to allow users to adjust pricing or quantity on contract line. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Busin...

Login failed on local SQL server
Hi all I've got a SQl server instance on my local pc which I use for development before transferring to test/production servers. I have full administrative rights. I've been using it for about a year with no problems until yesterday. I tried opening SQL server management studio and got the message "Login failed for user 'myid'". I've checked my event viewer from Control Panel | Administrative Tools and there's not much further information other than Event ID 18456, Ctegory 4. I've googled and the only help out there indicates that the err...

Virtual PC Additions Additions
Hey there people of wonderland. I've been trying to install the Virtual PC Additions after installing VPC 6.0.156.0 2007 and setting up two Virtual PCs with Windows 7 and Linux Feroda Core, Host being XP Pro 2002 SP3. When selecting "Action" | "Install/Update ..." nothing happens. then following the manual steps stated in the summary of "If the installation does not automatically start..." then again, nothing happens. Can anyone please help? Many thanks, Jo Johann wrote: > Hey there people of wonderland. > I've been trying to install the Virtual ...

SQL in Excel?
In Excel 2000/XP is it possible to treat a range of cells as a table and run sql statements against it? for example in sheet1 I have 3 columns Item_code, quantity, amount. If I want to calculate the total quantity, and amount for every Item to appear in Sheet2 which I could get by the following SQL statement select item_code, sum(quantity),sum(amount) from table_name (or range of cells) group by item_code thx MAB, Very easy using an ODBC connection. I use ADo myself, others use other techniques. Here's an example using ADO, it copies the recordset to an array. In XL2002 you can cop...

User password rest
We want to allow our helpdesk the ability to reset our users passwords to a default value (which will mostly be a random value). Will these random password values get populated into the password history? thx Howdie! On 08.06.2010 03:00, thebatchman wrote: > We want to allow our helpdesk the ability to reset our users passwords to a > default value (which will mostly be a random value). Will these random > password values get populated into the password history? The process for allowing the password reset is using "Delegation of Control" and its wizard. ...

Message size differ for user(user of exchange 2003) with outlook 2003 and 2007
Hi, When we configure the user of exchange server 2003 in Outlook 2007. we observe that message size is different with Outlook 2003. example for exchange server user (testuser) when we configure that in Outlook 2003 for a mail it show size(824). when we configure the same user in Outlook 2007 for same mail it shows size 3429. if some have faced such problem. or know why its happening. similarly i observe same user is configured on outlook 2007 on different machine the mail size differs.so what does size indicate and what all the parameters it depends Any suggestion or pointer could be gre...

additional SQL instances/best practices
Hello: Like most firms, we create test databases for clients that contain a copy of the client's live company's data. Someone suggested that we should first create a separate SQL instance, stating that Best Practices maintains that it is best to have a separate instance of GP SQL databases. Does Best Practices really maintain that it is best to have a separate instance as a Great Plains test environment? Also, isn't it much harder to maintain a test environment if the test company is in a separate instance of SQL server? And, if a client does have a separate instance of...

SQL Report using data from Multiple GP Companies
Is it possible to create a SQL Reporting Services report that will pull the same fields of data from more than one GP company? For instance, we have a customer that would like to pull Fixed Asset information from multiple GP companies. (Asset ID, Asset Description, Original Life in Years, Placed in Service Date, Cost Basis, Accumulated Depreciation, Net Book Value). I would assume you would have to create a data connections for each GP Company. But then would you create a separate table on the report for each company? Or would you use subreports? Just wondering if anyone has done it and...

User representation in exported messages using ExMerge
When i export messages to a PST via ExMerge, any of the local users to that exchange server, show up in the messages with the AD locator information rather than the users email address. Is there any way to make this not happen? I am trying to export and have the actual email address in the message. ...

Barcode Addition
I have acquired a Truetype barcode font. Is there a way to create and include address barcodes in or out of Publisher? -- Maurice <Maurice@discussions.microsoft.com> was very recently heard to utter: > I have acquired a Truetype barcode font. Is there a way to create and > include address barcodes in or out of Publisher? An article: http://office.microsoft.com/en-us/assistance/HP011269721033.aspx -- Ed Bennett - MVP Microsoft Publisher I would suggest that you read up on barcodes as there is way more to it than having a barcode font.. -- Mike Hall MVP - Windows Sh...

Create SQL temp table then open it using Dex?
I'm wanting to have a stored proc create a temp table and insert records. Then I'd like to use that temp table in Dexterity. This wouldn't involve report writer, it's all code. Here's my thoughts on the code: * call stored proc from dex * the stored proc puts data in a temp table and puts the temp table name in an OUT param * dex uses the value in the OUT param to do a 'open table BLAH with name [param value]' Is something like this possible? Thanks. Bryce This is possible, but because Dex is fussy about it's table structure, it is actually better to let...

Syntax Error in SQL Code for Email Alert
I am attempting to set up a SQL job that sends me an email alert each time a certain customer makes a payment. I am getting the following syntax error Msg 102, Level 15, State 1, Line 45 Incorrect syntax near '12'. with the following code. Can someone please help me troubleshoot it? The query by itself produces the desired result in SQL Server Management Studio, but throws this error when I add the code for the email. Any other comments or improvements are welcome. GP10, SP3, SQL Server 2005. Thanks --------------------------------------------------------...

Additional Licenses
I recently purchased licenses for 2 additional lanes for a client's RMS Store Ops system. They were sent a new (parallel) dongle. When they try to log on to POS on the 2 additional registers the system acts as if it is still only licensed for 1 lane & says register 1 already logged on. Is there something they must to to have RMS recognize the additional licenses? You might try uninstalling the dongle driver( rainbow) and reinstalling it. So it sees the new one. "Ron Bender" <Ron Bender@discussions.microsoft.com> wrote in message news:F6081F9C-FE5C-4146-A01C-9DB6...

addition
When I change a number in one of the cells the result won't change when I click "enter'". (this is in addition) press F9 and see what happens, if it calculates then you should do this: goto tools,options, calculations, set calculations to automatic -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708 View this thread: http://www.excelforum.com/showthread.php?threadid=519017 ...

Waitstats Performance on sql 2005
If one was to track wait times for span of an hour, what should be an acceptable wait percentage based on total waits for the following resources: ASYNC_NETWORK_IO CXPACKET SLEEP_BPOOL_FLUSH WRITELOG PAGEIOLATCH_EX PAGEIOLATCH_SH Thanks in advance There is to my knowledge no 'ratios' given as best practices between the listed wait types. It has been my experience (which is VERY deep on this topic) that it is almost always completely obvious which one(s) of the waits is really the problem indicator. I assume you ...

groups(creating users)
Make sure all the services that crm use are running under the account you installed crm with(domain admin). Make sure sql services run under that account as well. Also add your CRM server and domain user account to the pre-windows 2000 group*. Graham ...

Moving a user's mailbox to a new domain
Greetings, We have an Exchange 2000 server in a 2000 domain, and have to move some of the users to an Exchange 2003 server which is in its own domain. We would like to re-attach the mailbox to a user in the 2003 domain, or move the domain user ID from the 2000 domain to the 2003 domain, if possible. Both Exchange servers and domains 'live' in the same Active Directory forrest, same OU. Any pointers will be greatly appreciated! Dov ...

Log of users who open the workbook
Hi y'all, I used the search command and found several of your answers to this question, but each of them failed in the VBA for some reason. Yes, I'm in the VBA part (Alt+F11), I double clicked on ThisWorkbook, a window opened and I pasted the given code, one at a time, into the window, saved, exited the book, re-opened the book, and get an error each time. I dont really need to track changes, since I'll be the only one making any changes. The other user(s) who open this are only there to get information that I've entered, nothing else. I'd just like to know the...

Presence Detection
HI, I am running Exchange 2K3 with Outlook 2003. I have precense detection working (Call Work etc...) I would like to add a link to my companies portal web page in the Additional Actions window. Currently it says "No additional actions found", so I assume you should be able to add tasks/actions here. Is this possible, if so how is this done? Thanks ...

SQL Reporting Services and Multiple Databases
Creating a report to work against a single database is pretty easy. Does anybody have a good method for making the same report work for any company database? I'm not talking about consolidating them but being able to have the report set up once and make it dynamically use the company I specify. -- Charles Allen, MVP Charles, I saw a technique like this one http://www.sqlservercentral.com/articles/Development/2945/ at SQL Saturday in Orlando so I know it can be done. Essentially you're passing a parameter to the connection string similar to using a parameter in a report. Mark ...

sql server 2005
I want like to know how to do the following in sql server report service 2005: 1. Setup a main report and have it pass selected parameters to various subreports. 2. Setup a table of contents for a report that has a main report and various subreports. http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-reporting-services-part-1/ - --Reporting Services http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-reporting-services-part-2/ - --Reporting Services http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-reporting-services-part...