Trying to find restrictive way of giving user Sysadmin permissions. (1 Viewer)

Awesomo

Registered User.
Local time
Today, 23:10
Joined
Feb 9, 2016
Messages
45
Hi
This is tied to an earlier post I had, see http://www.access-programmers.co.uk/forums/showthread.php?t=284480

In summary - I'm using Access 2010 ADP with SQL Server 2008 R2.
If a user isn't a member of the Sysadmin server role, Access ADP reports will run slowly for that user. This wasn't an issue when users were on Access 2003.
After days of Google, the only solution I've found online is add users to the Sysadmin Server role. Then their reports speed right up.
I haven't found any other solution.
In addition to this Access_Reports database, there are other several databases on the server. I don't want to give users full rights to those. ( I'd rather not give Sysadmins permission to Access_Reports either, but needs must.)

Is there a way I can just give Sysadmin permissions to only the Access_Reports database?
Or a way to just give this permission when Access launches?

Thanks
 

Minty

AWF VIP
Local time
Today, 23:10
Joined
Jul 26, 2013
Messages
10,355
Have you tried / assuming its not too much effort / to not use an ADP? They are depreciated in later versions anyway?

Have you checked that each SP / view / table has domain user select privileges set on the SQL server? If not it may be authenticating them via some torturous route that slows it all down.
 

Awesomo

Registered User.
Local time
Today, 23:10
Joined
Feb 9, 2016
Messages
45
I'm recommending we move off ADP, but it will be the end of the year before that work can being. And that's several months of moaning too long for my users. But your your point is valid.

I've have just checked the select privileges, and its not set at all. Good idea!
All the domain users are set up as db_owner, which is probably how they are accessing the tables.
So I can remove them as db_owner. There about 100 tables - is there a way to apply the Select permissions to all the tables at once? I'll be working off the development backup, of course.

EDIT: Actually, I'll figure this one out. Its a while since I did any SQL security work, but I'll spend some time on it today and tomorrow. I should be using Roles, will check the existing roles that are set up.
 
Last edited:

Awesomo

Registered User.
Local time
Today, 23:10
Joined
Feb 9, 2016
Messages
45
Thanks for those links, very helpful.
I created a new role, granted the permissions and then added my users to that role.
Unfortunately, it made no difference to the speed. But at least I have a proper security model now!
Adding the Sysadmin role still increases the speed of the reports.

Not sure about the next step.
If I still want to test adding Sysadmin to just this one test database, is that possible?
Or is there something I should check?
 

Minty

AWF VIP
Local time
Today, 23:10
Joined
Jul 26, 2013
Messages
10,355
That's very strange - It must be an ADP thing - we use normal domain roles for privileges here and I've not seen that behaviour. We are using 2010 runtime with SQL Server 2008.
 

yupstrips

New member
Local time
Today, 16:10
Joined
Mar 26, 2016
Messages
9
Great your form posting .i really like you your perfact views.thanks alot.
 

Awesomo

Registered User.
Local time
Today, 23:10
Joined
Feb 9, 2016
Messages
45
Minty, it is just an ADP issue.
I've seen other mentions of it, like
http://answers.microsoft.com/en-us/...s/edbdffd1-55b0-40d5-9583-35ea515c37f8?auth=1
http://www.pcreview.co.uk/threads/a...s-cached-on-first-view-or-table-open.3832564/

In the second link the guy mentions blocking select permissions on Sysreferences.
I tried this, using the newly created Access_Reports Role as mentioned above.
That is, I right clicked on sys.sysReferences in SSMS and selected Deny for the Access_Reports role. But it made no difference.

Back to my earlier question - can I just give Sysadmin equivalent permissions on one database? It might help if I got it working, then I could work backwards to see what rights I can then remove.
 

Minty

AWF VIP
Local time
Today, 23:10
Joined
Jul 26, 2013
Messages
10,355
I honestly don't know - there are database level security settings - so I guess you could create sysadmin Role on that database and then add users to it.

Having had a quick look sysadmin only appears at server level, not at database level, but that maybe doesn't mean you can't add it?
 

SQL_Hell

SQL Server DBA
Local time
Today, 23:10
Joined
Dec 4, 2003
Messages
1,360
You cannot add sysadmin to a database on its own, it is a server level role which by default gives all access to all databases, there is nothing you can do to change this behaviour.

Curious to know how adding sysadmin makes the ADP go faster, really doesn't make a lot of sense. Bit of a headscratcher to be honest.
 

WayneRyan

AWF VIP
Local time
Today, 23:10
Joined
Nov 19, 2002
Messages
7,122
It doesn't make sense that the SQL server user defines the performance ...
Unless they're using the Resource Governor and giving The systems folks a
Larger piece of the pie.

Wayne
 

Rx_

Nothing In Moderation
Local time
Today, 17:10
Joined
Oct 22, 2009
Messages
2,803
http://www.access-programmers.co.uk/forums/showthread.php?t=259139&highlight=priority
While on a break, had to peek into the forum.
The code to boost priority on the microprocessor thread is on the startup form.
While the deployment is run on Citrix, the same code runs on the desktop during development and testing.
MSOFFICE including MSAccess yields to many other process. Probably so those pesky advertisements or endless stream of Microsoft Updates can have a time-slice.
When using Linked Tables (ODBC / SQL Server Native Client) that brings data back into any formula on a query or brings data back to be processed in Access - There is so much processing resources left idle.
Using the code above will boost the priority thread. While my reports are actually code intensive Excel VBA and queries used for business rule data mining, they in many ways mimic the Access reports - grouping headers and sub-headers that are based on business rules instead of field data.
The boost priority won't speed up the SQL Server side, but there is often a significant bottleneck in processing on the client side if the data is not just a copy / paste of a recordset.

Required disclaimer: By the way, "officially" I am in Paris at the moment.
 

Awesomo

Registered User.
Local time
Today, 23:10
Joined
Feb 9, 2016
Messages
45
You cannot add sysadmin to a database on its own, it is a server level role which by default gives all access to all databases, there is nothing you can do to change this behaviour.

Curious to know how adding sysadmin makes the ADP go faster, really doesn't make a lot of sense. Bit of a headscratcher to be honest.

Ok, thanks for that. Better I know that's is not an option.
Only vague reference I've seen is that its because of Sysreferences. I've tried that solution as well, just in case, but it made no difference.

http://www.pcreview.co.uk/threads/a...s-cached-on-first-view-or-table-open.3832564/

So yes, a bit of a head scratcher.
 

Awesomo

Registered User.
Local time
Today, 23:10
Joined
Feb 9, 2016
Messages
45
http://www.access-programmers.co.uk/forums/showthread.php?t=259139&highlight=priority
While on a break, had to peek into the forum.
The code to boost priority on the microprocessor thread is on the startup form.
While the deployment is run on Citrix, the same code runs on the desktop during development and testing.
MSOFFICE including MSAccess yields to many other process. Probably so those pesky advertisements or endless stream of Microsoft Updates can have a time-slice.
When using Linked Tables (ODBC / SQL Server Native Client) that brings data back into any formula on a query or brings data back to be processed in Access - There is so much processing resources left idle.
Using the code above will boost the priority thread. While my reports are actually code intensive Excel VBA and queries used for business rule data mining, they in many ways mimic the Access reports - grouping headers and sub-headers that are based on business rules instead of field data.
The boost priority won't speed up the SQL Server side, but there is often a significant bottleneck in processing on the client side if the data is not just a copy / paste of a recordset.

Required disclaimer: By the way, "officially" I am in Paris at the moment.

Thanks, I hope you're enjoying Paris.
I note that you use impersonate in that code. Would I be able to impersonate a Sysadmin user just while the Access report was running? Or even while Access itself was running.
That may well solve my issue.
 

SQL_Hell

SQL Server DBA
Local time
Today, 23:10
Joined
Dec 4, 2003
Messages
1,360
I am wondering if this is something to do with execution plans, it is entirely possible for one user to have a cached execution plan that differs from another users execution plan even though they are both running the same code. One performs well and one doesn't.....I have definitely seen this type of thing before.

Have you tried running the T-SQL or stored procedure that runs the report manually in SQL Server Management studio with sysadmin permissions and without sysadmin permissions to see if you get the same result?

That would give us a clear picture of whether this problem is localised around ADP or whether it is something else causing the issue.
 

Awesomo

Registered User.
Local time
Today, 23:10
Joined
Feb 9, 2016
Messages
45
Apologies for delay in coming back on this.
There are several sub reports involved. So the main report is based on the Clients table, with the sub reports based on different tables.
How would I go about testing this within SQL Server?
 

Users who are viewing this thread

Top Bottom