"Charles Wang[MSFT]" wrote:

> Hi Jason,
> Thanks for your post.
>
> From your description, I understand that:
> Your developers could not load SQL Profiler although you had assigned ALTER
> TRACE permission to the logins. When you added them to local administrators
> group, they can run it successfully.
> If I have misunderstood, please let me know.
>
> In your description, you mentioned:
> "The group has SysAdmin access on the server and each of the developers can
> do anything on the server except 2 things. They can't login with SQL
> Profiler and they can't import SSIS packages. These two things may be
> related or not but I'm wondering if giving access through Active directory
> groups is causing the problem??"
>
> Did you mean that you assign ALTER TRACE permission to the Active Directory
> user groups in SQL Server, but not each developer's domain account?
> I performed a test at my side and everything worked fine. Since
> establishing a domain environment may require a long time via our Lab, I
> just performed a simple test on my local computer. My test steps are as
> follows:
> 1. On my local computer, created a group named TestGroup;
> 2. Created a user named test and assigned it to the TestGroup;
> 3. Logged on SQL Server 2005, and added the group MyComputer\TestGroup as a
> login to the Security->Logins folder;
> 4. Granted the ALTER TRACE permission to the group login account:
>      Use Master
>      Go
>      GRANT ALTER TRACE TO [MyComputer\TestGroup]
>      Go
> 5. In Start menu, right click SQL Profiler, select "Run as...", and input
> [MyComputer\test] as the user to run SQL Profiler;
> 6. After SQL Profiler opened, select Windows Authentication (the login
> account was [MyComputer\test]) and click Connect.
>
> Then I found that it worked fine. SQL Profiler could monitor SQL Server
> 2005 activities correctly. I also tried adding another windows user account
> to the TestGroup and then run SQL Profiler as the new user and it also
> worked. I also checked the permissions requirement for Profiler in SQL
> Server 2005 Books Online and it said that to run SQL Server Profiler, users
> must be granted the ALTER TRACE permission. Combined with my test, I
> believe that it is not necessary to assign them to your local
> administrators group to run SQL Profiler.
>
> Is it possible that there are some Domain group policies to prevent that
> you run SQL Profiler from non-administrator account? You may contact your
> domain network administrator for further investigation. Also you may
> manually run Process Monitor to monitor your SQL Profiler process (Filter:
> Process Name = PROFILER90.EXE) to see if there are file or registry
> permission issues. If you would like, you could mail it to me
> (changliw_at_microsoft_dot_com) for further research. You can download
> Process Monitor from:
> Process Monitor v1.25
> http://www.microsoft.com/technet/sysinternals/utilities/processmonitor.mspx
>
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>

"Charles Wang[MSFT]" wrote:

> Hi Jason,
> Just clarify that since I will be on a vacation in the next few days, if
> you have collected the Process Monitor logs, please mail it to Wei
> (weilu_at_microsoft_dot_com). He will work with you for your further
> assistance.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>

"Jason" <JRawlins@noemail.nospam> wrote in message
news:F66262B3-4767-401C-80D8-AA99225E5DDF@microsoft.com...
> Hi,
>
> I have a SQL Server that is causing some issues when devleopers attempt to
> load SQL profiler. I am an admin on the server and have no problems at all.
>
> I have read many articles about granting permission to "Alter Trace" but I
> have done that with no joy. I am wondering if this is due to the way we give
> access to the servers. We use Active Directory groups and add any developers
> who need access to this group. The group has SysAdmin access on the server
> and each of the developers can do anything on the server except 2 things.
> They can't login with SQL Profiler and they can't import SSIS packages. These
> two things may be related or not but I'm wondering if giving access through
> Active directory groups is causing the problem??
>
> If I add a developer to the Local Administrator group they can run profiler.
> So what permissions do I need to give someone to be able to do this without
> NT administror access??
>
> The group everyone belongs to is SysAdmin and have the permission Alter
> Trace granted..
>
> Your help would be greatly appreciated...
>
> Thanks
> Jason
>

"Tibor Karaszi" wrote:

> I would have one of them to login using their windows account, open a query window and verify
> whether they are sysadmin (using the IS_SRVROLEMEMBER function).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Jason" <JRawlins@noemail.nospam> wrote in message
> news:F66262B3-4767-401C-80D8-AA99225E5DDF@microsoft.com...
> > Hi,
> >
> > I have a SQL Server that is causing some issues when devleopers attempt to
> > load SQL profiler. I am an admin on the server and have no problems at all.
> >
> > I have read many articles about granting permission to "Alter Trace" but I
> > have done that with no joy. I am wondering if this is due to the way we give
> > access to the servers. We use Active Directory groups and add any developers
> > who need access to this group. The group has SysAdmin access on the server
> > and each of the developers can do anything on the server except 2 things.
> > They can't login with SQL Profiler and they can't import SSIS packages. These
> > two things may be related or not but I'm wondering if giving access through
> > Active directory groups is causing the problem??
> >
> > If I add a developer to the Local Administrator group they can run profiler.
> > So what permissions do I need to give someone to be able to do this without
> > NT administror access??
> >
> > The group everyone belongs to is SysAdmin and have the permission Alter
> > Trace granted..
> >
> > Your help would be greatly appreciated...
> >
> > Thanks
> > Jason
> >
>

"Erland Sommarskog" wrote:

> Jason (JRawlins@noemail.nospam) writes:
> > Function returns 1 for both of us. I can login to Profiler he get an
> > Invalid login
> 
> "Invalid login"?
>
> When I tried to log in with plain-vanilla user I got a message box
> telling me that I needed ALTER TRACE or be a sysadmin member. If your
> colleague is getting Invalid login, he appears to be logging into some
> server where does not have any login.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

"Erland Sommarskog" wrote:

> Jason (JRawlins@noemail.nospam) writes:
> > Don't know what to tell you...If I add this developer to the local admin
> > group he can login to profiler. if I remove him and rely on the access
> > enabled through the Active Directory group which does give him sysadmin
> > then he gets an invalid login.
>
> Could you post the exact error message? (There is a button down to the left
> that permits you to put the message on the clipboard.)
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns99D426C52312Yazorman@127.0.0.1...
> Jason (JRawlins@noemail.nospam) writes:
>> Not much details I'm afraid but here you are:
>>
>>==========================================
>>
>> Cannot connection to xxxServer.
>>
>>==========================================
>> Login failed for user 'xxxDomain\xxxuser'. (pfutil90)
>
> So that means that he fails to log into the server at all. If the problem
> was ALTER TRACE, there would be a different message box (and one that does
> not have copy-to-clipboard).
>
> Since you say that he can log into Mgmt Studio to the same server, I
> can see two possibilities:
>
> 1) Profiler fails to pass the required tokens for Windows authentication
>   to succeed.
> 2) He is after all trying to log into some other server.
>
> The first sounds very strange. I would imgagine that Profiler logs like
> anyone else, that is through a client API. Hm, I see in
> sys.dm_exec_sessions that Profiler uses OLE DB. And you said he had
> problems
> in SSIS as well.
>
> Could you have hime try with SQLCMD and OSQL -E from the command line?
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx