How to Run SQL Profiler with non-admin users

A user needs to run SQL Profiler, how can enable him to run this tool
without assign admin rights? or Any alternative way? Thanks.Eco
I' affraid you cannot run it without being a members of sysadmin
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Uri Dimant wrote:
> Eco
> I' affraid you cannot run it without being a members of sysadmin
>
>
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
> >A user needs to run SQL Profiler, how can enable him to run this tool
> > without assign admin rights? or Any alternative way? Thanks.
> >
> >
You must be a member of sysadmin fixed server role or have alter trace
permissions.
Regards
Amish Shah
http://shahamishm.tripod.com|||Sysadmin role membership is a requirement in SQL 2000. In SQL 2005, you can
grant ALTER TRACE to the user's login.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi Eco
We have a tool called SQLBenchmarkPro that is designed precisely to meet
this requirement - it's a service based Trace management tool that allows
users to run traces against SQL 2000 (& SQL 2005) without needing sa
permissions. It also allows many other high-end trace management features,
such as scheduling, central collection, performance analysis etc. You can
download an eval at: www.GAJSoftware.com
Regards,
Greg Linwood
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||ALL NOTED. Thank you very much!!
"Greg Linwood" <g_linwood@.hotmail.com> ¦b¶l¥ó
news:OrNU4IfwGHA.4968@.TK2MSFTNGP03.phx.gbl ¤¤¼¶¼g...
> Hi Eco
> We have a tool called SQLBenchmarkPro that is designed precisely to meet
> this requirement - it's a service based Trace management tool that allows
> users to run traces against SQL 2000 (& SQL 2005) without needing sa
> permissions. It also allows many other high-end trace management features,
> such as scheduling, central collection, performance analysis etc. You can
> download an eval at: www.GAJSoftware.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
> >A user needs to run SQL Profiler, how can enable him to run this tool
> > without assign admin rights? or Any alternative way? Thanks.
> >
> >
>

How to Run SQL Profiler with non-admin users

A user needs to run SQL Profiler, how can enable him to run this tool
without assign admin rights? or Any alternative way? Thanks.Eco
I' affraid you cannot run it without being a members of sysadmin
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Uri Dimant wrote:
[vbcol=seagreen]
> Eco
> I' affraid you cannot run it without being a members of sysadmin
>
>
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
You must be a member of sysadmin fixed server role or have alter trace
permissions.
Regards
Amish Shah
http://shahamishm.tripod.com|||Sysadmin role membership is a requirement in SQL 2000. In SQL 2005, you can
grant ALTER TRACE to the user's login.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi Eco
We have a tool called SQLBenchmarkPro that is designed precisely to meet
this requirement - it's a service based Trace management tool that allows
users to run traces against SQL 2000 (& SQL 2005) without needing sa
permissions. It also allows many other high-end trace management features,
such as scheduling, central collection, performance analysis etc. You can
download an eval at: www.GAJSoftware.com
Regards,
Greg Linwood
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi
With SQL 2005 there is an ALTER TRACE permission that allows you create
traces.
John
"unknown" wrote:

>|||ALL NOTED. Thank you very much!!
"Greg Linwood" <g_linwood@.hotmail.com> bl
news:OrNU4IfwGHA.4968@.TK2MSFTNGP03.phx.gbl g...
> Hi Eco
> We have a tool called SQLBenchmarkPro that is designed precisely to meet
> this requirement - it's a service based Trace management tool that allows
> users to run traces against SQL 2000 (& SQL 2005) without needing sa
> permissions. It also allows many other high-end trace management features,
> such as scheduling, central collection, performance analysis etc. You can
> download an eval at: www.GAJSoftware.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>

How to revoke Rights in Mixed Mode Authentication

Hi all,
we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
Authentication.
Revoking rights to database users using SQL-Server logins does not seem to
work properly.
When I log in as TinyTim with SQL-Server authentification I can happily
create views on the database (TinyTim is assigned to), select, insert and
update in ForbiddenTable, ...
Output of sp_helpuser 'TinyTim'
UserName GroupName LoginName DefDBMame UserID
----
--
TinyTim Auswerter NULL NULL 5
Output of sp_helprotect, entries related to user TinyTim
Owner Object Grantee Granto ProtectType Action Column
----
--
dbo ForbiddenTable Auswerter dbo Deny Insert .
dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
dbo ForbiddenTable TinyTim dbo Deny Delete .
dbo ForbiddenTable TinyTim dbo Deny Insert .
dbo ForbiddenTable TinyTim dbo Deny References(All+New)
dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
. . public dbo Deny Backup Database .
. . public dbo Deny Backup Transaction .
. . public dbo Deny Create Default .
. . public dbo Deny Create Function .
. . public dbo Deny Create Procedure .
. . public dbo Deny Create Rule .
. . public dbo Deny Create Table .
. . public dbo Deny Create View .
Thanks in advance
Regards,
ArneArne,
Is TinyTim a member of the sysadmin role? You can't deny anyhing to
sysadmins.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:3DEB6B87-3D28-40D8-9262-AB527E486F47@.microsoft.com...
> Hi all,
> we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
> Authentication.
> Revoking rights to database users using SQL-Server logins does not seem to
> work properly.
> When I log in as TinyTim with SQL-Server authentification I can happily
> create views on the database (TinyTim is assigned to), select, insert and
> update in ForbiddenTable, ...
>
> Output of sp_helpuser 'TinyTim'
> UserName GroupName LoginName DefDBMame UserID
> ----
--
> TinyTim Auswerter NULL NULL 5
> Output of sp_helprotect, entries related to user TinyTim
> Owner Object Grantee Granto ProtectType Action Column
> ----
--
> dbo ForbiddenTable Auswerter dbo Deny Insert .
> dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Delete .
> dbo ForbiddenTable TinyTim dbo Deny Insert .
> dbo ForbiddenTable TinyTim dbo Deny References(All+New)
> dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
> . . public dbo Deny Backup Database .
> . . public dbo Deny Backup Transaction .
> . . public dbo Deny Create Default .
> . . public dbo Deny Create Function .
> . . public dbo Deny Create Procedure .
> . . public dbo Deny Create Rule .
> . . public dbo Deny Create Table .
> . . public dbo Deny Create View .
>
> Thanks in advance
> Regards,
> Arne
>|||"Dejan Sarka" wrote:

> Arne,
> Is TinyTim a member of the sysadmin role? You can't deny anyhing to
> sysadmins.
Hi Dejan,
promising shot but: TinyTim is not a member of any server role
Arne|||Arne,
I can't reproduce this. Can you generate T-SQL script for the table,
database users, logins and permissons? It can be done quickly in EM, just
right-click on the table and select All Tasks -> Generate SQLScripts. Then
in Options menu select optins for logins, users and permissions. Post the
script here, so we can check it.
Thanks,
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:861933B6-FB6E-44C5-8674-470764833EE7@.microsoft.com...
> "Dejan Sarka" wrote:
>
> Hi Dejan,
> promising shot but: TinyTim is not a member of any server role
> Arne|||> Arne,
> I can't reproduce this. Can you generate T-SQL script for the table,
> database users, logins and permissons? It can be done quickly in EM, just
Hi Dejan,
the generated script does not contain the settings for the 'public' role -
(just did not come out by the described procedure) the properties of the
database ProsimTest show red X's in each checkbox - hope the rest of it
helps.
In case it is relevant: we are using the standard edition of SQL Server.
Regards,
Arne
if not exists (select * from master.dbo.syslogins where loginname =
N'TinyTim')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'ProsimTest', @.loginlang = N'Deutsch'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
END
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
GO
if not exists (select * from dbo.sysusers where name = N'Any' and uid < 1638
2)
EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
GO
if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid <
16382)
EXEC sp_grantdbaccess N'TinyTim'
GO
DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
[TinyTim]
GO
if not exists (select * from dbo.sysusers where name = N'Auswerter' and uid
> 16399)
EXEC sp_addrole N'Auswerter'
GO
exec sp_addrolemember N'Auswerter', N'TinyTim'
GO
CREATE TABLE [dbo].[ForbiddenTable] (
[ID] [int] NOT NULL ,
[Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
GO
DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswerter
] CASCADE
GO
DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
[dbo].[ForbiddenTable] TO [TinyTim] CASCADE
GO|||Arne,
I run the script on my SQL Server and I still can't reprduce the problem.
Here TinyTim is denied everything. I use SQL 2k Dev edtion with SP3a. Maybe
you should contact support?
Sorry I couldn't help more.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:81A8E360-A236-44CB-A66C-7C5E43BD2BB6@.microsoft.com...
just[vbcol=seagreen]
> Hi Dejan,
> the generated script does not contain the settings for the 'public' role -
> (just did not come out by the described procedure) the properties of the
> database ProsimTest show red X's in each checkbox - hope the rest of it
> helps.
> In case it is relevant: we are using the standard edition of SQL Server.
>
> Regards,
> Arne
>
> if not exists (select * from master.dbo.syslogins where loginname =
> N'TinyTim')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'ProsimTest', @.loginlang = N'Deutsch'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
> END
> GO
>
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
> GO
>
> if not exists (select * from dbo.sysusers where name = N'Any' and uid <
16382)
> EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
> GO
> if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid
<
> 16382)
> EXEC sp_grantdbaccess N'TinyTim'
> GO
> DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
> DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
> [TinyTim]
> GO
> if not exists (select * from dbo.sysusers where name = N'Auswerter' and
uid
> EXEC sp_addrole N'Auswerter'
> GO
> exec sp_addrolemember N'Auswerter', N'TinyTim'
> GO
> CREATE TABLE [dbo].[ForbiddenTable] (
> [ID] [int] NOT NULL ,
> [Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
> GO
> DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswert
er] CASCADE
> GO
> DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
> [dbo].[ForbiddenTable] TO [TinyTim] CASCADE
> GO
>

how to restrict developers from create / delete tables

Can anybody tell me a simple way of removing CREATE/DELETE table rights.
I want developers to be able to do anything but CREATE/DELETE tables.
Thank you.Well by default no one has any abilities in SQL Server. So if they can
already create and delete tables you must have put them in a role that has
these permissions. Chances are you made them dbo? If so you need to remove
them from the dbo role and assign them to a role that has what permissions
you want.
Andrew J. Kelly SQL MVP
"UnkleVo" <isharko@.att.net> wrote in message
news:a3538463.0407140656.53522073@.posting.google.com...
> Can anybody tell me a simple way of removing CREATE/DELETE table rights.
> I want developers to be able to do anything but CREATE/DELETE tables.
>
> Thank you.