End User can access entire data without any cube roles

Hi Experts,

I have a report model on top of a cube. I had set a role for an end user to view some specific data which was not working, i then deleted the role from the cube. The end user can still see the entire set of data. Have anyone faced this scenario. The security doesnt seem to work at all.

Please advise.

Regards

Neeraj

Hi Neeraj,

Do you have Kerberos authentication set up? If not, and if Analysis Services is not running on the Report Server, the Report Server is likely using a fixed ID (not the end-user ID) to access Analysis Services - you could confirm the ID by tracing the login to Analysis Services in SQL Profiler.

Enabling SQL audit Logging for SELECT queries

Hi all,
We have a requirement in our project where we need to audit log any query
(SELECT queries inclusive) that are fired on a specific set of objects (Tabl
e
and views) in our database. We need to capture information like Who fired th
e
query, When and the actual query itself.
The approach we have thought of is:
Run SQL Profiler and log the output of the trace into a SQL table
Create an INSERT trigger on the SQL table.
Trigger should write data into a custom Audit table with limited information
.
However the divantages we see here are performance issues due to profiler
being run continuously, maintenance overhead to clear the SQL table where th
e
trace is written etc.
Can anyone suggest any other better alternative for this requirement?
Thanks
GSNot sure if it ius an option for you, but SQL 2005 contains DML
triggers which allow you to audit Select statements.
Markus|||How about using any of the 3:rd party tools put there? Check the log reader
tools, they tend to have
this support (possibly in special versions): http://www.karaszi.com/SQLServer/link
s.asp
These tools does use the transaction log to audit modifications and Profiler
to audit SELECT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GS" <GS@.discussions.microsoft.com> wrote in message
news:6A359D6A-CCD1-4EC5-AEA2-C74C7F551971@.microsoft.com...
> Hi all,
> We have a requirement in our project where we need to audit log any query
> (SELECT queries inclusive) that are fired on a specific set of objects (Ta
ble
> and views) in our database. We need to capture information like Who fired
the
> query, When and the actual query itself.
> The approach we have thought of is:
> Run SQL Profiler and log the output of the trace into a SQL table
> Create an INSERT trigger on the SQL table.
> Trigger should write data into a custom Audit table with limited informati
on.
> However the divantages we see here are performance issues due to profil
er
> being run continuously, maintenance overhead to clear the SQL table where
the
> trace is written etc.
> Can anyone suggest any other better alternative for this requirement?
> Thanks
> GS
>

Enable/disable logging dynamically

Is there a way to turn on/off a given logger dynamically across the board, and still enable the logging of task specific events, like "ExecuteSQLExecutingQuery" from the ExecuteSQL task or "WMIDataReaderOperation" from the WMI Data Reader task?

The use case is one where multiple loggers are defined in a package (custom and stock) and I'd like to toggle the execution tracing on a per logger basis.

I realize "logger toggling" can be done programmatically, but I'm wondering if that's the only way to accomplish the objective.

The behaviour of the logging cannot be modified at runtime. Also, logging different events in different log providers for same executable is not possible as well. It's a great idea for a future version tough.

What you can do is decide who can log, what log providers should each executable use, and what events for each executable you want to log at design time. (e.g.: I can log "ExecuteSQLExecutingQuery" from the ExecuteSQL task in SQL server, and WMIDataReaderOperation" from the WMI Data Reader task in a file).

Thanks,

Ovidiu

|||

You may also consider investigating creating a custom logging solution using Event Handlers. You have a great deal of control over how they operate and you can even use variables and configurations to control them.

Jamie Thomson had a blog about this sometime back... here is the link: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

Enable/disable logging dynamically

Is there a way to turn on/off a given logger dynamically across the board, and still enable the logging of task specific events, like "ExecuteSQLExecutingQuery" from the ExecuteSQL task or "WMIDataReaderOperation" from the WMI Data Reader task?

The use case is one where multiple loggers are defined in a package (custom and stock) and I'd like to toggle the execution tracing on a per logger basis.

I realize "logger toggling" can be done programmatically, but I'm wondering if that's the only way to accomplish the objective.

The behaviour of the logging cannot be modified at runtime. Also, logging different events in different log providers for same executable is not possible as well. It's a great idea for a future version tough.

What you can do is decide who can log, what log providers should each executable use, and what events for each executable you want to log at design time. (e.g.: I can log "ExecuteSQLExecutingQuery" from the ExecuteSQL task in SQL server, and WMIDataReaderOperation" from the WMI Data Reader task in a file).

Thanks,

Ovidiu

|||

You may also consider investigating creating a custom logging solution using Event Handlers. You have a great deal of control over how they operate and you can even use variables and configurations to control them.

Jamie Thomson had a blog about this sometime back... here is the link: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

Enable the CLR at database level only

Hi,

Is there a way to allow specific databases access to the CLR. Currently, my understanding is that when this setting is enabled, it applies to all databases within the instance.

Kind regards,

Jan.

CLR is enabled by instance, you could sepearate the database by instances whereas one would allow, the other one would t allow the CLR.

Jens K. Suessmeyer

http://www.sqlserver2005.de

enable TCPIP Protocols by script

How do you enable TCPIP in Protocols for Network Config for the sql server
2005 and for all the specific IP addressses via T-SQL script.> How do you enable TCPIP in Protocols for Network Config for the sql server
> 2005 and for all the specific IP addressses via T-SQL script.
I do not know for T-SQL, but I guess you can do it with SMO or WMI. Here is
a VBScript script that uses WMI to enlist the protoclos and enable named
Pipes:
' enum protocols and show status
set wmi =
GetObject("WINMGMTS:\\. \root\Microsoft\SqlServer\ComputerManage
ment")
for each prop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'mssqlserver'")
WScript.Echo prop.ProtocolName & " - " & _
prop.ProtocolDisplayName & " " & _
prop.Enabled
next
' enable named pipes
for each changeprop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'mssqlserver' and " & _
"ProtocolName = 'Np'")
changeprop.SetEnable()
next
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||BTW, you have to restart the service if you want changes in network
protocols to take effect.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eRGorcHRHHA.412@.TK2MSFTNGP02.phx.gbl...
> I do not know for T-SQL, but I guess you can do it with SMO or WMI. Here
> is a VBScript script that uses WMI to enlist the protoclos and enable
> named Pipes:
> ' enum protocols and show status
> set wmi =
> GetObject("WINMGMTS:\\. \root\Microsoft\SqlServer\ComputerManage
ment")
> for each prop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver'")
> WScript.Echo prop.ProtocolName & " - " & _
> prop.ProtocolDisplayName & " " & _
> prop.Enabled
> next
> ' enable named pipes
> for each changeprop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver' and " & _
> "ProtocolName = 'Np'")
> changeprop.SetEnable()
> next
>
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>|||This does not enable the TCPIP, change the enable setting from NO to YES in
the TCP/IP properties. Do you have to do any update statement in WMI? I
haven't used WMI before. I did stop and restart my sql server.
I changed your code from Np to tcp as I think that was for named pipes not
tcp .
Any help would be gratefully received
thanks
"Dejan Sarka" wrote:

> I do not know for T-SQL, but I guess you can do it with SMO or WMI. Here i
s
> a VBScript script that uses WMI to enlist the protoclos and enable named
> Pipes:
> ' enum protocols and show status
> set wmi =
> GetObject("WINMGMTS:\\. \root\Microsoft\SqlServer\ComputerManage
ment")
> for each prop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver'")
> WScript.Echo prop.ProtocolName & " - " & _
> prop.ProtocolDisplayName & " " & _
> prop.Enabled
> next
> ' enable named pipes
> for each changeprop in wmi.ExecQuery("select * " & _
> "from ServerNetworkProtocol " & _
> "where InstanceName = 'mssqlserver' and " & _
> "ProtocolName = 'Np'")
> changeprop.SetEnable()
> next
>
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>