Rights to start/stop a service

We have installed MSDE on 6 work PCs for use in a modeling project.
Since I have admin rights through the domain I did the install.
However, since the MSDE service runs as local system account and the
user who is logging in has no special rights the service won't start.
As a fix I went back and set the service to run as myself
DOMAIN\wawork which at first seemed worked. I would log out and the
modeling user would log in. The first time the SQL Server service
starts up fine. Then when he reboots the PC the next day it will no
longer startup. Not for that user or for me when I log back in as
myself. If I change it back to log on as local system account,
start/stop it, then set it to run as me DOMAIN\wawork it starts
working again. Why I don't know
When it stops working the error is:
The service did not start due to a logon failure.
or
Could not start the MSSQLSERVER service on local computer Error 5:
Access is denied.
So I'm hoping if the user who needs to run the models can be given
access to start/stop services we can leave MSDE set to run as local
system account. Does anyone know what rights need to be set to make
this happen? Our windows administrator can't find a place to give
users that right.
Thanks,
wawork@.hotmail.com
h Randy,
Randy K wrote:
> We have installed MSDE on 6 work PCs for use in a modeling project.
> Since I have admin rights through the domain I did the install.
> However, since the MSDE service runs as local system account and the
> user who is logging in has no special rights the service won't start.
>
> As a fix I went back and set the service to run as myself
> DOMAIN\wawork which at first seemed worked. I would log out and the
> modeling user would log in. The first time the SQL Server service
> starts up fine. Then when he reboots the PC the next day it will no
> longer startup. Not for that user or for me when I log back in as
> myself. If I change it back to log on as local system account,
> start/stop it, then set it to run as me DOMAIN\wawork it starts
> working again. Why I don't know
> When it stops working the error is:
> The service did not start due to a logon failure.
> or
> Could not start the MSSQLSERVER service on local computer Error 5:
> Access is denied.
> So I'm hoping if the user who needs to run the models can be given
> access to start/stop services we can leave MSDE set to run as local
> system account. Does anyone know what rights need to be set to make
> this happen? Our windows administrator can't find a place to give
> users that right.
you can have a look at
http://msdn.microsoft.com/library/de...rview_6k1f.asp
or better http://support.microsoft.com/?id=283811, but sp4 introduced
something new and the SQL Server Agent can no longer start if you deny the
local administrator access to the instance... still investigating about
that..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Rights for clickonce Report Builder access?

I'm attempting to grant rights to Report Builder as deployed as part of my TFS install. My problem is that I have to add my users to the builtin\admin group in order for them to see the report builder button on the SQL Server Reporting homepage & then have rights to launch the ReportBuilder click once app. I obviously do not want make users admins on the box, but I've tried adding them to all of the other groups having to do with SQLServer to no avail. How can I grant users access to launch the Report Builder app?

Users need to have the Report Server system permission "Execute Report Definitions" in order to see the Report Builder icon in Report Manager.

1. access Report Manager Site Settings page: http://%machine%/Reports/Pages/Settings.aspx

2. click "Configure site-wide security"

From here you can either modify an existing RS role assignment or create a new one, and give the applicable users permissions to "Execute Report Definitions".

BTW, the Windows security groups created by SQL Server setup aren't used for RS security. Members of the local Administrators get some built-in permissions, which is why making your users local Administrators caused the Report Builder icon to show up.

|||I started with that: the users see the report builder Icon, but then they are prompted with a Sql server logon when the click the report builder icon. I've granted datareader on the sql databases 'tfswarehouse' and 'reportServer' for that user. Is it a sql rights thing?|||

No, I don't think that's going to make a difference. The data that's used in the Report Builder reports is accessed using credentials as per the data source object in Report Server that's bound to the model that Report Builder is consuming.

Making sure I have the right repro:

1. click Report Builder icon in Report Manager

2. see a SQL Server logon dialog

You never see a model selection pane or a design surface?

|||

That's correct. If I give box admin rights to user, all looks and acts normally. With Report builder admin rights & no box admin rights the user sees report builder icon but when he goes to lauch the app he gets security dialog.

|||

Resolved it. Maybe it was me being dense, but the report builder site security page does not make any sense to me. You need to first of all create a group (box or domain) and add the users to it. then use that group name in the security page granting the content manager right. It makes total sense to me now that it's done, but it's not clear on the security page that that is what you need to do. I'd suggest making that page more clear... or the documentation... or both!

Rights assigned to Windows\Power Users in SQL Express.

Hi,

In SQL Server Express Edition, what are the rights that are assigned to a Normal Windows User and PowerUser by default ?

When I install SQL Express on a clean machine and login as Power User I can add/edit/delete data but when I login as Normal Windows User I can see the data but not change it.

Please help me in this regard.

Thanks and Regards,
Gautham.

What do you mean exactly by the ability to add/edit/delete data; what kind of data is that?

In SQL Server Express, members of Builtin\Administrators are members of the sysadmin server role. Members of Builtin\Users are only granted the connect sql permission. There is no special provisioning for Builtin\Power Users, so they would just have the same right on SQL Server Express as a "normal" user.

To verify if a principal has a special permission, you can use the has_perms_by_name builtin. For example, to check if a user has select on table t, you can execute:

select has_perms_by_name ('t', 'object', 'select')

while connected as that user.

For additional information, you can check the following catalogs:

sys.server_principals
sys.server_permissions
sys.login_token
sys.database_principals
sys.database_permissions
sys.user_token

Thanks
Laurentiusql

Reverting From MSSQL 2005 to 2000-Help please....

I have installed MSSQL 2005 on my D: drive of my windows 2003 server, but now need to install MSSQL version 2000 b/c I realized I have one less license than I thought I had. I have build no databases, only the default information. Can I install another instance (being the 2000 instance) on the same drive? If I can't, can I install it on the Crive without a lot of issues? Or would it be easier to remove 2005 and just start from scratch? The sql version is 9.0. Any help or comments would be greatly appreciated.!!!!!

It 'should' be OK to have both versions.

However, if you are not planning to use the SQL 2005 because of licensing issues, it may prove easier to just remove it. (My attitude is that if there is no license for it, then it shouldn't be on a computer (who knows when the licenising 'police' may show up...)

|||There are no problems running SQL 2005 and SQL 2000 on the same machine and the same drive.