Interface Blogs

Occasionally you would like to find out all the tables, views and stored procedures a user can access and what permissions they have.  With a combination of the new EXECUTE AS command and the new HAS_PERMS_BY_NAME function you can figure this out.  Below is a query that uses the sys.All_Objects metadata view to generate object names.  Then it passes the object names through the new function with some additional parameters (the securable type, and permission type) and it returns 1 if the user has that permission and 0 if they do not.

The result shows both implicit permissions granted through fixed server or database roles. It also shows explicit permission granted though user-defined roles or granted directly to the database user account. 

The EXECUTE AS can only specify individual user principal defined to SQL Server.  It cannot reference a Windows Group.  This is only valid with SQL Server 2005. 

EXECUTE AS LOGIN = 'miami\anders'

SELECT SCHEMA_NAME(schema_id) + '.' + name TableName
, type_desc
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
    'OBJECT', 'SELECT') AS have_select
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
    'OBJECT', 'UPDATE') AS have_update
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
    'OBJECT', 'INSERT') AS have_insert
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
    'OBJECT', 'DELETE') AS have_delete
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
    'OBJECT', 'EXECUTE') AS have_execute
FROM sys.all_objects
WHERE type_desc IN ('USER_TABLE', 'SQL_STORED_PROCEDURE', 'VIEW')
AND SCHEMA_NAME(schema_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY type_desc, tablename

REVERT

Here are some queries using the new Dynamic Management views and functions see which indexes are being used and how they are being updated. 

This query shows which indexes are used, whether it was a seek, scan or lookup and the date it was last performed. When using the object_name function, you want to USE the database you are interested in and then filter the view so it only shows objects from that database.  Filtering on object_id > 100 eliminates system tables from the output.

USE Adventureworks

SELECT object_name(object_id), *
FROM sys.dm_db_index_usage_stats
WHERE object_id > 100 AND database_id = DB_ID('Adventureworks')

This query provides more information about current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.   Since this query uses a table-valued function, you must pass parameters for the database id, table id, index id, and partition number.  If you want to select all tables in a database you just need to pass the database id and specify DEFAULT for the other three parameters.  Filtering on object_id > 100 eliminates system tables from the output.

SELECT object_name(object_id), *
FROM sys.dm_db_index_operational_stats (db_id('Adventureworks'), default, default, default)
WHERE object_id > 100

The last query provides read and write I/O counts by file.  This can help you determine which files have the highest I/O activity. 

SELECT db_name(database_id), *
FROM sys.dm_io_virtual_file_stats(default, default)

Defining security for SQL Server Integration Services (SSIS) is a bit complex. I want to focus on the security required by a developer to create and manage their own packages and people that are not SysAdmins but need to manage all packages.

After a package has been developed using BI Development Studio, it must be deployed to a location for subsequent execution. This is a similar model to application dll’s and exe’s that must be deployed to an object library for execution. With SSIS you can deploy a package to any file system directory, the MSDB database or a special file system directory known by the Integration Services Service. When deploying to a file system directory (whether known by Integration Services Service or not), you must rely on OS file system security to control access to these packages. If you deploy to the MSDB database you have other mechanisms available to control access to packages.

The MSDB database has three predefined fixed database roles specifically designed for SSIS. They are db_dtsltduser, db_dtsoperator and db_dtsadmin. Each of the roles has execute permissions on a set of MSDB stored procedures that are used to manipulate packages located in the dbo.sysdtspackages90 table. You can also define your own custom database roles to further secure packages deployed to MSDB. The user needs a database user account in MSDB to be assigned to these roles.

Each package has a default assignment to both the readerrole and writerrole. The readerrole allows for enumerating a packaging (displaying its name), viewing a package’s definition, executing a package interactively, exporting a package definition and running a package under SQL Server Agent as a job. The writerrole allows for importing a package, deleting a package and changing package security roles.

When a package is placed in the MSDB database a default role assignment is made. The package’s readerrole is assigned to db_dtsadmin, db_dtsoperator and the creator of the package. The writerrole is assigned to db_dtsadmin and the creator of the package. The details for each role and what they can do is described in SQL Server 2005 Books Online under the “Integration Services Roles” topic.

For those developers that need to deploy packages to MSDB, enumerate and execute packages that they own, they need to a member of db_dtsltduser. Again they can only manipulate packages that they own.

If a person that is not a Sysadmin needs to administer all the packages stored in MSDB, they need to be in the db_dtsadmin role. All users with SysAdmin rights can see and do anything with packages.

To execute any package, the user must be in the db_dtsoperator role.

Additional user-defined database roles can be created in MSDB to provide an additional level of control. The additional roles work in conjunction with the fixed roles described above. By default, when a package is placed in the MSDB database, the owner of the package and the users in the db_dtsadmin role can view the package definition (via exporting), modify the package (via importing), and execute the package. Also users in the db_dtsoperator role can execute the package. When you assign a user-defined role as the readerrole, anyone in that user-defined role can view the package and execute it. When you assign a user-defined role as the writerrole, anyone in that user-defined role can update the package definition. The user must also be in the db_dtsltduser role. So users must be in both the user-defined role and the db_dtsltduser role to access packages.

For example, you have a development team creating Integration Services packages. They decide to deploy the packages to SQL Server MSDB for execution. The packages where created by different developers and therefore the packages have different creators/owners. We want the entire team to be able to read and potentially update the various packages. The DBA does not want to give the developers SysAdmin permissions nor the ability to read, update and execute all the packages in the MSDB database. One approach would be to:

1. Create a user-defined role in MSDB, place all the developers in that role.

2. Assign that role to the db_dtsltduser fixed database role. This allows the developer team to access packages that they created and own.

3. Assign each package the team created to the user-defined role created earlier.

This will allow all team members to access all associated packages no matter who created them.

There are other issues related to the encryption of sensitive data in the package that I will cover in another blog post.

One aspect of using the PIVOT is to include a list of columns that will be used as the crosstab of the query.  For example, if you wish to display the total quantity ordered by Productid by Year you need to include the list of years for the query.  This unfortunately must be a static list of values.  So as you add years to the table, the query must be modified to include the new year.  We discussed different ways to build a dynamic query to generate the list of years based on the data and insert that list into the PIVOT clause. 

Following are a couple of scripts to solve the problem.  The first one generates a sum of orderqty by productid pivoted by the duedate year.  The second one generate a sum and avg of orderqty by productid by the duedate year.  This one must UNION two PIVOT queries into a single result set.

-- Dynamically build the list of years based on the years in
-- the purchasing.purchaseorderdetail table. 
-- Then use the list to perform a PIVOT operation displaying the
-- ProductID in each row and the sum of orderqty by Year.

DECLARE @Yearlist VARCHAR(MAX)
DECLARE @Years TABLE (Year int)

INSERT @Years (year)
 SELECT DISTINCT top 100 percent YEAR(duedate)
 FROM purchasing.purchaseorderdetail
 ORDER BY YEAR(duedate)

SELECT @YearList = COALESCE(@YearList + ',[', '[') +
CAST(year AS CHAR(4)) + ']'
FROM @Years t

EXEC ('SELECT *
FROM (SELECT productid, YEAR(duedate) theyear, orderqty
FROM purchasing.PurchaseOrderDetail) t
PIVOT (SUM(OrderQty) FOR theyear IN (' + @Yearlist + ')) PVT')

-- Dynamically build the list of years based on the years in
-- the purchasing.purchaseorderdetail table. 
-- Then use the list to perform a PIVOT operation displaying the
-- ProductID in each row and the sum of orderqty by Year.
-- Union this result with the same query performing an AVG on the
-- orderqty by productid. Included a literal column to identify
-- if the data is a sum or average.

DECLARE @Yearlist VARCHAR(MAX)
DECLARE @Years TABLE (Year int)

INSERT @Years (year)
 SELECT DISTINCT top 100 percent YEAR(duedate)
 FROM purchasing.purchaseorderdetail
 ORDER BY YEAR(duedate)

SELECT @YearList = COALESCE(@YearList + ',[', '[') +
CAST(year AS CHAR(4)) + ']'
FROM @Years t

EXEC ('SELECT *
 FROM (SELECT  productid, ''sum'' type, YEAR(duedate) theyear, orderqty
  FROM purchasing.PurchaseOrderDetail) t
 PIVOT (SUM(OrderQty) FOR theyear IN (' + @Yearlist + ')) PVT'
 + 'UNION ' +
 'SELECT *
 FROM (SELECT  productid, ''avg'' type, YEAR(duedate) theyear, orderqty
  FROM purchasing.PurchaseOrderDetail) t
 PIVOT (AVG(OrderQty) FOR theyear IN (' + @Yearlist + ')) PVT ' +
 'ORDER BY productid')

There is one more technique for generating the comma-delimited list of years using the FOR XML PATH option of a SELECT statement.  I found this technique in Itzik Ben-Gan's new T-SQL Querying book.  I decided to apply it to our dynamic pivot problem to see if we can come up with a simpler way to generate the list of years for the PIVOT operator. I quess we can debate whether this is simpler, at least it gets rid of the need to create a table variable and eliminates a statement.

This technique takes advantage of the PATH option on the FOR XML clause.  If you specify an empty string in the PATH option it does not place any XML tags around the text.  The STUFF function deletes the first comma generated in the string. 

Following is a rewrite of the simple pivot above using this technique.

DECLARE @Yearlist VARCHAR(MAX)

SELECT
@Yearlist = STUFF((SELECT ', [' + DYear + ']' AS [text()]
FROM (SELECT DISTINCT
      CONVERT(VARCHAR, YEAR(duedate)) dyear  
      FROM purchasing.purchaseorderdetail) Y   
      ORDER BY dyear
      FOR XML PATH('')), 1, 1, '')

EXEC ('SELECT *
FROM (SELECT productid, YEAR(duedate) theyear, orderqty
FROM purchasing.PurchaseOrderDetail) t
PIVOT (SUM(orderqty) FOR theyear IN (' + @Yearlist + ')) PVT')

Treat Yourself to Fn_Split()

The link above is to an article in SQL Server magazine on a user-defined function called fn_split(). This function allows you to pass a string of values to an IN clause of a SQL query. It breaks them down into a set of rows in a virtual table. Then you use a subquery in the IN clause.

Following is a T-SQL script you can use to rename your SQL Server after you have changed the computer name. This script will work if you are connected to the default instance or a named instance. After running the script, you must restart SQL Server for the action to complete.

DECLARE @machinename sysname, @servername sysname, @instancename sysname
SELECT @instancename =
        CASE 
            WHEN charindex('\', @@servername) = 0 THEN '' 
            ELSE SUBSTRING(@@servername, 
                 CHARINDEX('\', @@servername), (len(@@servername)+ 1) - 
                 CHARINDEX('\', @@servername)) 
            END
SET @machinename = convert(nvarchar(100), serverproperty('machinename')) + @instancename;
EXEC sp_dropserver @@servername;
EXEC sp_addserver @machinename, 'local'

Moving logins from SQL Server to SQL Server has to be done alot.  There are a number of situations that require moving logins like: log shipping, moving a database from one server to another, or database mirroring.  In SQL Server 2000 DTS Transfer Logins didn't seem to do the job I wanted.  The SQL Server 2005 Integration Services product is closer but it doesn't move SQL login passwords.  

So I wrote a set of SQL statements that creates a script of sp_addlogins to copy the passwords and SIDs across for SQL Server 2000 and another query that creates the same script for SQL Server 2005 using CREATE LOGIN.   The generated script includes Window logins, denied Windows logins and sets all server role memberships.  If you are moving a database from one instance of SQL Server to another instance this script can help.  It has been tested on SQL Server 2000 running SP3a and SQL Server 2005 running SP1.  I would run this script before detaching databases (if you are using the detach/attach approach) and then run the resulting script on the new instance after you have attached or restored the databases to the new server. 

This script moves all logins (except the SA, builtin\administrators, administrative local groups (SQL Server 2005)  and nt authority\system logins) for all databases defined to an instance of SQL Server. If you are running this script with Query Analyzer or Management Studio, make sure the maximum characters per column is set to 8000.  

Now for the disclaimer. I offer this script with no implied support nor has it been extensively tested.  You should thoroughly review the script generated before applying it to your system.  You can use this script for the intended purpose and also as a model for how you can use SQL to write scripts using a database table as the source.

I hope it helps.

Click HERE to get the SQL Server 2000 script file.

Click HERE to get the SQL Server 2005 script file. 

The SQL Server 2000 statistics are available in a pseudo-table in Master called sysperfinfo. This table returns the statistics in a table format. There are some statistics that represent ratios (i.e., Buffer Cache Hit Ratio). These statitics must combine two rows. One row provides the data value and the second row provides a divisor. Therefore to be able to read ratio statistics, you must bring these two rows together to compute the final result.

Included in this post is a SELECT statement that displays all the statistics as well as perform the necessary calculation to display ratio statistics correctly.

This query can come in handy if you need to view SQL Server statistics when you don't have access to the server's performance monitor program. It can also allow you to collect SQL Server statistics for storage in a SQL table for subsequent analysis. Just add a column with a GUID or DateTime stamp to group each statistics snapshot.

I just found that all the statistics that are time adjusted like Logins/sec, Batches/sec, Transactions/sec, etc. are not displayed properly through a query of Master..Sysperfinfo. It will display a monotonically increasing number since the start of SQL Server. There is no way to compute the per second value. This also means that you cannot set SQL Server Alerts that use a per second threshold from these statistics to fire the alert. I noticed this behavior when doing more testing on my query and then found article 555064 in the Microsoft Knowledge Base confirming what I saw. This reduces the usefulness of this query. But you can still get access to most of the statistics.

SELECT p1.object_name
, p1.counter_name
, p1.instance_name
, CASE p1.cntr_type
            WHEN 537003008 -- Count is a ratio
                 THEN CONVERT(FLOAT, p1.cntr_value) / 
                       CASE p2.cntr_value
                           WHEN 0 THEN 1 -- Make sure we don't divide by zero
                            ELSE p2.cntr_value
                       END
   ELSE p1.cntr_value -- No calculation necessary, display value
END AS Value
FROM master..sysperfinfo p1 
     LEFT OUTER JOIN master..sysperfinfo p2
         ON (SUBSTRING(p1.counter_name , 1, --Deal with ratios that has (ms) in name
             COALESCE(NULLIF(charindex(' (ms)',p1.counter_name), 0), len(p1.counter_name) + 1))
            = SUBSTRING(p2.counter_name, 1, CHARINDEX(' Base', p2.counter_name))) --Remove Base from counter name
           AND p1.instance_name = p2.instance_name
           AND p2.cntr_type = 1073939459 -- Only join if row is a ratio
WHERE p1.cntr_type 1073939459 -- Don't include rows with base divisor value

Download Reporting Services Scripter

In class this week, one of the students told me about an automated scriptor for SQL Server 2000 Reporting Services. So I checked it out and wanted to pass it along to anyone actually reading this blog.

If you go to the link above, it will take you to a website describing the RSScriptor program. It's a .NET program that connects to your Reporting Services server and gives you an MMC view of all your Reporting Services objects. You can then select the objects, specify what you want to script in the Options section and it will generate .RSS scripts that use the RS.EXE program. It also generates a .CMD file that executes RS.EXE with the proper parameters.

This tool can make it easier to move reports, datasources, and resources from a development server to production. And the best part is that it builds and underlying script that can be reexecuted multiple times.

It's also a great way to learn how to code some of the Reporting Services web service methods.

Thank you DK for the reference.

In my SQL Server 2005 DBA Upgrade class I demonstrated the use of the new partitioning feature with a rolling range scenario. The rolling range scenario that I used keeps 6 months of orders and order detail data. The tables are partitioned based on orderdate where each partition holds one month of data. When a new month of data is added to the table, the oldest month of data is dropped out.

The power of partitioning comes from the ability to manipulate large chunks of data in an even larger table with only metadata operations. These metadata operations are very fast. The old data can be switched out in a second. Then after loading the new months data into the proper filegroup, it can be switched into the larger multi-month table in a second.

The script that I used to demonstrate partitioning is included HERE.

This script demonstrates both Left and Right based partitioning functions.

I have a bunch of DTS packages sitting in the MSDB database on my SQL Server 2000 system.  I want to move the packages to my SQL Server 2005 server to continue executing them while I begin the migration.  It looks like I need to generate a .dts file from the SQL 2000 MSDB and then import them one at a time into SQL Server 2005's MSDB. 

Well, there is a better way.  You can use Integration Services to move them.  In SQL Server 2000 the packages are stored in the table dbo.sysdtspackages in MSDB.  This table is exactly the same as dbo.sysdtspackages in SQL Server 2005.  This table is where the SQL Server 2005 upgrade process places DTS packages from SQL Server 2000.  Integration Services packages are placed in the table dbo.sysdtspackages90. 

So all we have to do is build a very simple package that copies the rows from the dbo.sysdtspackages in SQL Server 2000 to SQL Server 2005.  Define a new Integrations Services package, add a data flow to it.  Then add an OLE DB source adapter that points to your SQL Server 2000 MSDB database.  If you want to copy all packages and all their versions, include the query "SELECT * FROM dbo.sysdtspackages".  If you want just the latest package versions you can use the following query:

SELECT t1.*
FROM dbo.sysdtspackages as t1
     INNER JOIN  (SELECT [name]
                         , [id]
                         , MAX([createdate]) as [createdate]
                         FROM dbo.sysdtspackages
                         GROUP BY [name], [id]) AS t2
            ON t1.[id] = t2.[id]
                AND t1.[createdate] = t2.[createdate]

The OLE DB Source Adapter connects directly to an OLE DB Destination Adapter mapping all the columns across.  The Destination Adapter must connect to your SQL Server 2005 MSDB database.  After defining the connection you will try to select the dbo.sysdtspackages table in the destination but it will not show up in the drop down list.  I found you need to define a variable at the package level using the string data type and provide a value of "dbo.sysdtspackages".  Then go back to your destination's Data Access Mode drop down list and select "Table name and view name variable".  This will activate the Variable name drop down list.  Select the variable you just created with the dbo.sysdtspackages value.

Run this package and it will copy those packages from SQL Server 2000 to SQL Server 2005.  They will show up in the Management/Legacy/Data Transformations Services folder in SQL Server 2005 Management Studio.  To view and edit the DTS packages download the Microsoft SQL Server 2000 DTS Designer Components through the following link: http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

If you have trouble opening DTS packages after installing this component check out Knowledge Base note 917406 for a possible resolution. 

 

 

I got a question in class to as to how to figure out the owner of a stored procedure or a view. After looking at the sys.all_objects system view, saw that the principal_id column had a NULL in each row. SQL Server 2000 BOL says that for Views and Store Procedures the Principal_ID column value will be NULL if the object is owned by the schema owner. If the object ownership has been changed with the ALTER AUTHORIZATION statement, it will have a value other than NULL.

So I wrote a query that will display the object owner for views and stored procedures whether it is owned by the schema owner (whoever that is) or is owned by an account other than the schema owner.

Here is the query:

SELECT s.name + '.' + o.name AS ObjectName
-- Display schema owner or overidden owner
, COALESCE(p.name, p2.name) AS OwnerName
FROM sys.all_objects o
       LEFT OUTER JOIN sys.database_principals p
            ON o.principal_id = p.principal_id
       LEFT OUTER JOIN sys.schemas s
           ON o.schema_id = s.schema_id
       LEFT OUTER JOIN sys.database_principals p2
           ON s.principal_id = p2.principal_id
WHERE o.type IN ('V', 'P')
AND s.name NOT IN ('sys', 'INFORMATION_SCHEMA')

One of the features of SQL Server 2005 that does not get a lot of press is the new window-based calculation clause called OVER. You can now generate different aggregates with different grouping columns in the same query, without having to use subqueries. This is especially useful when you need to include aggregated values along side detail data in the same result set or compute percentages based on different groupings.

The OVER clause is mostly used when performing the new RANK, DENSE_RANK, ROW_NUMBER and NTILE functions. But it can be used with standard scalar aggregates like SUM, AVG, etc..

The following is an example of using the OVER clause with the PARTITION BY to generate a couple of different aggregates each with a different grouping (partitioning).  If you have the Adventureworks database with SQL Server 2005 you can run these sample queries.

SELECT [group] CountryGroup
, name Territory
, SUM(salesytd) OVER (PARTITION BY [group], [name]) AS TerritoryTotal
, SUM(salesytd) OVER (PARTITION BY [group]) AS GroupTotal
, (SUM(salesytd) OVER (PARTITION BY [group], [name]) /
SUM(salesytd) OVER (PARTITION BY [group])) * 100.0 AS PercentofGroup
FROM sales.salesTerritory
ORDER BY [group]

The following is an equivalent query using derived table subqueries:

SELECT g.[group] CountryGroup
, t.name Territory
, TerritoryTotal
, GroupTotal
, TerritoryTotal / GroupTotal * 100.0 PercentofGroup
FROM (SELECT [group]
              , SUM(salesytd) GroupTotal
               FROM sales.salesterritory
               GROUP BY [group]) g
           INNER JOIN
                 (SELECT [group]
                  , name
                  , SUM(salesytd) TerritoryTotal
                  FROM sales.salesterritory
                  GROUP BY [group], name) t
                         ON g.[group] = t.[group]
ORDER BY [group], name