[SQL2005] Profiler Trace Security Issue

With SQL Server 2005 there is an option to grant a person access to Profiler for tracing SQL. This is done with the "GRANT ALTER TRACE" statement. The statement has to be executed at server level i.e. the master database.

The user in question only has access to certain databases on that server. The security problem that arises is that with the Profiler rights active, he can see the sql commands that are executed on the databases he has no rights for. Those SQL commands are executed by others users.

How do I configure security rules so that the person in question can use Profiler, but can only see the SQL statements that are executed on the databases he has the rights for? TIA!

Unfortunately there is no such level of granularity in SQL Server 2005 permission model, and ALTER TRACE is considered an elevated permission because of the reasons you mentioned.

Hopefully somebody may be able to reply back with a good workaround.

-Raul Garcia

SDE/T

SQL Server Engine

[help] ALTER TABLE: error message 4929

Hi,

I've the following error message:

-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go

Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----

This is only part of a script with much more alter table…

How can I lunch the script without this problem?

Thanks for your help!
Giacomo

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Well, you cant alter schema involved in replication all that easily. For
this, you can use sp_addmergecolumn (not certain if the name is correct but
its close). If you post other code samples we can try to see what can be
done

MC

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgst4zpwt6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

>
Hi,
>
I've the following error message:
>
-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go
>
Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----
>
This is only part of a script with much more alter table.
>
How can I lunch the script without this problem?
>
Thanks for your help!
Giacomo
>
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

|||I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.|||One of the downsides to replication is that is complicates subsequent schema
changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.

If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards. It's
unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.

|||Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for you
so we could suggest something.

MC

PS. Dan, its MC (Marko Culo) ;).

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

One of the downsides to replication is that is complicates subsequent

schema

Quote:

Originally Posted by

changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.
>
If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards.

It's

Quote:

Originally Posted by

unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER

TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use

replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.

>
>

|||PS. Dan, its MC (Marko Culo) ;).

Oops. Sorry, Marko.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:eg0r1t$bqq$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for
you
so we could suggest something.
>
>
MC
>
PS. Dan, its MC (Marko Culo) ;).
>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

>One of the downsides to replication is that is complicates subsequent

schema

Quote:

Originally Posted by

>changes. As ML mentioned, you need to run the appropriate sp_repl* procs
>instead of ALTER TABLE. The details vary depending on the type of
>replication and the changes you are making.
>>
>If your client established replication outside the scope of your normal
>support agreement, I suggest you have then remove replication so that
>your
>normal script can be run and then reestablish replication afterwards.

It's

Quote:

Originally Posted by

>unreasonable to expect your upgrade script to handle a replicated schema
>unless you have detailed knowledge of the replication topology.
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"Giacomo" <no_spam@.grazie.itwrote in message
>news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER

TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use

replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.

>>
>>

>
>

[ask] trigger in sqlserver not working, HELPP!

i have this trigger in my database :

ALTER TRIGGER dbo.AddVoucher
ON dbo.User_AddVoucher
AFTER INSERT
AS
SET NOCOUNT ON;

DECLARE @.UserId int,
@.Add_id int,
@.voucher_id char,
@.Kredit money,
@.date smalldatetime,
@.last_balance money,
@.voucher_status char

SELECT @.UserId = UserId,
@.voucher_id = Voucher_ID,
@.Add_id = Add_id,
@.date = Deposit_Date
FROM Inserted

SELECT @.Kredit= Voucher_Value,
@.voucher_status = Voucher_Status
FROM Voucher
WHERE Voucher_ID = @.voucher_id

INSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date)
VALUES (@.Add_id,@.UserId, 'Kredit',@.date)

select @.last_balance = Balance
from User_Balance
WHERE UserId = @.UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @.UserId order by Update_Id DESC)

if (@.voucher_status = 'active')

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for trigger here
BEGIN
update User_Balance
set Balance = @.last_balance + @.Kredit
where AddVoucher_ID = @.Add_id

update Voucher
set Sold_Date = @.date
where Voucher_ID = @.voucher_id

END

ELSE
BEGIN
raiserror ('Voucher is not valid',0,1)
rollback transaction
END
go

the problem is the update function is not working and the if statement always put to 'FALSE'

do you think anything wrong with the code

Thats perhaps because of the way you have declared the @.voucher_status parameter. char by default is same as char(1). So your @.voucher_status will only have an "a" if the status was "active". So change your datatype to @.varchar(10).

|||

oh my god...i didn't notice it...just a little mistake there, thanks bro for your help....

it runs smoothly now...

@@ROWCOUNT

How can I return the number of rows returned by a select statement such as
the following one that returns zero in @.Qty ?
Alter Procedure GetMonitors
(
@.Qty INT OUTPUT
)
As
SELECT a.Suburb, a.State FROM
(
SELECT Suburb, State FROM Monitors
UNION
SELECT Suburb, State FROM Properties
WHERE Delisted Is Null
) a
GROUP BY Suburb, State
SET @.Qty = @.@.ROWCOUNT
return
Thanks in advance...Below work just fine for me... Did you define the parameter as out when you
call the proc?
USE pubs
GO
ALTER PROC p
@.qty int OUT
AS
SELECT au_lname, au_fname FROM authors
SET @.qty = @.@.ROWCOUNT
GO
--Prove that it works:
DECLARE @.rows int
EXEC p @.qty = @.rows OUT
PRINT @.rows
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"McHenry" <mchenry@.mchenry.com> wrote in message
news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> How can I return the number of rows returned by a select statement such as
the following one that
> returns zero in @.Qty ?
> Alter Procedure GetMonitors
> (
> @.Qty INT OUTPUT
> )
> As
> SELECT a.Suburb, a.State FROM
> (
> SELECT Suburb, State FROM Monitors
> UNION
> SELECT Suburb, State FROM Properties
> WHERE Delisted Is Null
> ) a
> GROUP BY Suburb, State
> SET @.Qty = @.@.ROWCOUNT
> return
>
> Thanks in advance...
>|||Thanks... my mistake !
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMCL8TXWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Below work just fine for me... Did you define the parameter as out when
> you call the proc?
> USE pubs
> GO
> ALTER PROC p
> @.qty int OUT
> AS
> SELECT au_lname, au_fname FROM authors
> SET @.qty = @.@.ROWCOUNT
> GO
> --Prove that it works:
> DECLARE @.rows int
> EXEC p @.qty = @.rows OUT
> PRINT @.rows
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "McHenry" <mchenry@.mchenry.com> wrote in message
> news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>