Cross-Database Ownership Chaining

Ownership chaining allows the management of multiple objects by setting permission on one object and then allowing the chain to control pass through access.

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Full example of Ownership chaining here

SQL Server can be configured to allow ownership chaining between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required.

Server Level

By setting the option at the server level, any settings for this option at a database level are ignored.

--Set instance wide database chaining
sp_configure 'cross db ownership chaining', 1
go
reconfigure with override
go

From the GUI, the option can be accessed from Server Properties | Security

image

Database Level

The DB_CHAINING option Controls whether the database can be accessed by external resources, such as objects from another database.

--set chaining for a database
alter database DB_MONITORING set DB_CHAINING ON
GO

From the GUI, the option can be accessed from

Database Properties | Options | Miscellaneous | Cross-Database Ownership Chaining Enabled (TRUE | FALSE )

image

The eagled eyed may notice that the Cross-Database Ownership Chaining option is greyed out. Im not sure why this is and tried to research further, but it seems that setting the option can only be done at a database level through the ALTER statement.

Advertisements

Accessing SQL 2008 Instances from SSMS 2005

Im a bit old skool in that i prefer to use the activity monitor in SSMS 2005 as oppose to the activity monitor in Resource Manager in 2008. I noticed the other day that this was made possible a while back in Cumulative update 5 of SP2 on 2005. (9.00.3215)

This related to BUG number 50002151, KB article 946127
FIX: You may experience problems when you use SQL Server Management Studio in SQL Server 2005 to connect to an instance of SQL Server 2008

I think caution has to be used in using old clients to manage newer releases but it serves a purpose for my activity monitor issue.

One of the good features that you would miss out on by using the above method is that SSMS 2008 has IntelliSense, but only when you connect to a SQL 2008 server. If you connect to a 2005 server, you don’t get IntelliSense.

Backing up Server Logins and Permissions

In a previous post, i talked about how to add WMI Alerts for Create Database and Create Login. Now if we want to take this a step further, we can create a job to extract out server logins and database permissions every time a new login is created.

Extracting Server Logins

This is fairly straightforward as Microsoft provide the sp_help_revlogin procedure to make this a painless task. The procedure and how to use it can be found here

This generates a easy to import list of all server logins including encrypted passwords. This can be particularly useful when migrating databases or moving instances.

Ive amended this procedure to add functionality to retrieve server roles

DECLARE @prname sysname DECLARE @pmname sysname DECLARE sr_curs CURSOR FOR SELECT pr.name, pm.name
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS pm ON rm.role_principal_id=pm.principal_id
JOIN sys.server_principals AS pr ON rm.member_principal_id=pr.principal_id
WHERE pr.name <> 'sa' AND pr.name NOT LIKE 'NT SERVICE%' AND pr.name NOT LIKE 'NT AUTHORITY%' OPEN sr_curs
FETCH NEXT FROM sr_curs INTO @prname, @pmname
WHILE (@@fetch_status <> -1) BEGIN SET @tmpstr = 'EXEC sp_addsrvrolemember '''+@prname+''', '''+@pmname+'''' PRINT @tmpstr
FETCH NEXT FROM sr_curs INTO @prname, @pmname
END CLOSE sr_curs
DEALLOCATE sr_curs

Extracting user permissions on databases

Many years ago i found a procedure developed by Clint Herring (SQL Server Central) which extracts out user permissions for a DB. I’ve been using it in SQL 2000, 2005 and 2008 without any issues.

sp_getUserInfo procedure available here

Then by simply running the procedure with a database variable input, it will list out all the permissions on that db.

sp_getuserinfo 'DB_Monitoring'

Extracting ALL database permissions and Server logins

So, to make life easier, i created a job which initially extracts server logins to a file, (CMDExec)

sqlcmd -E -S MSSQLSERVER\Instance -d master -Q “exec sp_help_revlogin” -o”D:\Log\Logins\Server_logins.sql”

and then created a 2nd step which extracted all user permissions for all databases to a file per database. (T-SQL)

Set Nocount on Declare @dbname varchar(100) Declare @servername varchar(100) Declare db Cursor For Select name from master.dbo.sysdatabases

Declare @osql varchar(1000) select @servername = @@servername Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
    Begin Set @osql='EXEC master.dbo.xp_cmdshell '+''''+'sqlcmd -E -S '+@servername+' -d DB_Monitoring -Q "sp_getuserinfo ['+@dbname+']" -o"D:\Log\Logins\'+@dbname+'.sql"'+'''' EXEC (@osql) --Execute the osql statement Fetch Next from db into @dbname
    End Close db

Deallocate db

Connecting the above to an Alert

Go into the Alert properties and add a response to execute the job

image

So now every time a new login is created, we back up all permissions and server logins!

Building a List from a result set to use as a column in another Query

…the title doesn’t explain the scenario very well but hopefully the description below will…

Scenario

A report needs to be generated which has a maximum of 1 row per order. Each order can have multiple voucher numbers, and these voucher numbers must be visible for that order on that single row.

At first glance, this isn’t possible. Why? because it is not possible to group unique voucher numbers which reside as different rows onto 1 row together with maintaining the integrity of the data. In the standard scenario, those voucher numbers will always group separately, hence creating multiple rows per order.

For example:

SELECT a.order_id, a.apar_id, sum(a.amount), a.voucher_no, a.sequence_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount
FROM acrtrans a INNER JOIN apodetail b
ON ( a.client=b.client
    and a.order_id=b.order_id
    and a.account=b.account
    and a.apar_id=b.apar_id) WHERE a.client = 'TM' AND a.order_id = '405604' GROUP BY a.order_id, a.apar_id, a.sequence_no, a.voucher_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount

image

As the above indicates its not possible to get this into one row, as the unique voucher numbers dictate the number of rows.

So how do we get all this info on to 1 row?

Im sure there are different methods but this worked ok for me:

First, the idea is to collate the voucher numbers for each order and present them on 1 row. We do this by creating a function to push in the order id, and extract the vouchers for that order id, and clean up and format into 1 column and row.

CREATE FUNCTION [dbo].[Get_Voucher_Numbers](@order_id int) RETURNS VARCHAR(1000) BEGIN DECLARE @VoucherList varchar(1000) SELECT @VoucherList = coalesce(@VoucherList + ', ', '') + CONVERT(varchar(8),a.voucher_no) FROM acrtrans a inner join apodetail b
        ON (a.client=b.client
        and a.order_id=b.order_id
        and a.account=b.account
        and a.apar_id=b.apar_id) where a.client = 'TM' AND a.order_id = @order_id
    ORDER BY a.voucher_no

RETURN @VoucherList

END GO

Running this for the example above returns the following:

image

Now the idea is to build this into the original SQL so that the voucher number becomes irrelevant in the group by.

SELECT a.order_id, a.apar_id, sum(a.amount), dbo.Get_Voucher_numbers(a.Order_id) AS Voucher_Numbers, a.sequence_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount
FROM acrtrans a INNER JOIN apodetail b
ON ( a.client=b.client
    and a.order_id=b.order_id
    and a.account=b.account
    and a.apar_id=b.apar_id) WHERE a.client = 'TM' AND a.order_id = '405604' GROUP BY a.order_id, a.apar_id, a.sequence_no, b.arr_amount, b.real_amount, b.com_amount, b.vow_amount

image

As can be seen above, we now see all 3 voucher numbers for that order on a single line.

If anyone has a better way of doing this please let me know… I did start looking at CTE but got a bit unstuck!

WMI Alerts for Create Database and Create Login

We had a need to know when a new database was created on a SQL Instance, and then further to find out whenever a login was created on a server.

My knowledge of WMI events is limited but i have managed to build a working alert for each of these conditions.

So, there are a couple of things to consider before WMI alerts will work:

    • Make sure service broker is enabled in msdb
-- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Disable Service Broker: ALTER DATABASE [Database Name] SET DISABLE_BROKER;

To check whether service broker is enabled

SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases;

If service broker is enabled the result will be 1. If not it will be 0.More information about Service broker and why it is required for WMI events is explained here

  • Make sure the SQL Server Agent is started and make sure that “REPLACE TOKENS FOR ALL JOB RESPONSES TO ALERTS” option is checked under Agent properties -> Alert system

image

or by running the following T-SQL

USE [msdb]
GO EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

New Database Created

USE [msdb]
GO /****** Object: Alert [New Database Created] Script Date: 06/06/2011 11:15:23 ******/ EXEC msdb.dbo.sp_add_alert @name=N'New Database Created', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @notification_message=N'Database $(ESCAPE_NONE(WMI(DatabaseName))) has been created on $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))', @category_name=N'[Uncategorized]', @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'Select * from CREATE_DATABASE' go EXEC msdb.dbo.sp_add_notification @alert_name=N'New Database Created', @operator_name=N'DBA Team', @notification_method = 1
GO

New Login Created

USE [msdb]
GO /****** Object: Alert [New Login Created] Script Date: 06/06/2011 11:35:37 ******/ EXEC msdb.dbo.sp_add_alert @name=N'New Login Created', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @notification_message=N'Login $(ESCAPE_NONE(WMI(ObjectName))) has been created on $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))', @category_name=N'[Uncategorized]', @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'Select * from DDL_USER_EVENTS' EXEC msdb.dbo.sp_add_notification @alert_name=N'New Login Created', @operator_name=N'DBA Team', @notification_method = 1
GO

The examples above us the namespace for a default instance of SQL Server. To run for a names instance, the namespace should look like this:

@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\InstanceNAME'

For full details of all the Data Definition Language events on Event Groups see here

If the alerting is not working and you have followed the example above correctly, try a restart of the SQL Server Agent to kick start the alerting.

Block Corruption – ORA-08102: index key not found

We had a refresh job which started failing with the following error:

Error at line 2
ORA-12008: error in materialized view refresh path ORA-08102: index key not found, obj# 6104122, file 364, block 27923 (2) ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 2

ORA-08102 is a mismatch between the key(s) stored in the index and the values stored in the table.

To resolve i first found out which object was affected:

select owner, object_name, object_type
from dba_objects
where object_id = 6104122;

Which returned the schema.I_MY_OBJECT

This was an index so then the next step was to rebuild the index

ALTER INDEX SCHEMA.I_MY_OBJECT rebuild;

but this didn’t resolve the error. Research found the following:

Rebuilding index will not work on most (if not in all) cases. Rebuilding index does not visit table at all. It uses existing index structure, to create new segment, so this problem will be transferred to new index as well.

In the end i dropped and recreated the index which seemed to resolve the error.