SQL2012 It’s The Small Things Pt4–User Defined Server Roles

Server roles have been around since before I started working with SQL Server however these roles have always been strictly defined and there has been no ability to either alter them or create new ones. SQL Server 2012 finally changes that and provides you with the opportunity to create server level roles that will let you provide greater permissions to logins without having to constantly deal with the minutiae of managing them on a login by login basis.

Let’s say for example you wanted to grant several logins the ability the view any definition. Normally this would require you to perform those grants on a very granular level. Now you can just create a server role and drop logins into that role:

USE [master]

GO

CREATE SERVER ROLE [SrvViewDefs]

GO

use [master]

GO

GRANT VIEW ANY DEFINITION TO [SrvViewDefs]

GO

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsql\Joe];

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsql\Biggles];

That’s some quick and easy management.

Here’s some examples of other permissions that can be quickly and easily provisioned to multiple users via user defined server roles:

• Alter any linked server
• Alter any login
• Alter any server audit
• Alter any server role
• Alter resources
• Alter server state
• Alter settings
• View any database
• Shutdown SQL Server

It goes beyond basic SQL Server permissions, you can also allow server roles to impersonate logins or manage Availability Groups

USE [master]

GO

CREATE SERVER ROLE [SrvImpersonate]

GO

use [master]

GO

GRANT IMPERSONATE ON LOGIN::[SIRSQL\nic] TO [SrvImpersonate]

GO

ALTER SERVER ROLE [SrvImpersonate] ADD MEMBER [sirsql\Biggles];

Anything that allows me to manage SQL Server more efficiently makes me happy. User defined server roles certainly make me happy.

SQL2012 It’s The Small Things Pt3–End Of The Month Function

If you work with financial data or have to do regular reporting type functions then I’m sure you currently use some kind of funky function/case statement to figure out what the last day of the month is and something even more funky to find the last day of next month or the month after.

SQL Server 2012 fixes that for you with the introduction of the EOMONTH function.

It’s very simple to use:

SELECT EOMONTH('2012/03/16')

Even better, the EOMONTH function can accept an additional parameter where you can specify a number of months to add or remove to get relevant results:

SELECT EOMONTH('2012/03/16', -1)

It even handled the leap year correctly.

Simplicity itself. A great addition.

SQL2012 It’s The Small Things Pt2–Unified Security Experience

Back when SQL Server 2005 came out there was a big change to the way that logins were handled. No more stored procedures to create a SQL login or to allow a Windows user or group to connect to SQL. It also brought the advent of allowing SQL logins to have the same kind of policy restrictions as you had in Active Directory.

Despite these huge changes for some reason Microsoft did not implement the same kind of changes when it came to roles. You still had to use stored procedures to grant role membership, this lead to an experience where you had to manage the syntax differently:

USE Master;

CREATE LOGIN [sirsql\DBAs] from windows WITH DEFAULT_DATABASE = TempDB;

USE tempdb;

CREATE USER [sirsql\DBAs];

CREATE ROLE RoleTest;

exec sp_addrolemember 'RoleTest', 'sirsql\DBAs';

exec sp_droprolemember 'RoleTest', 'sirsql\DBAs';

With SQL 2012 this old stored procedure way of managing things has finally gone by the wayside and we now manage roles in a similar fashion to logins:

USE Master;

CREATE LOGIN [sirsql\DBAs] from windows WITH DEFAULT_DATABASE = TempDB;

USE tempdb;

CREATE USER [sirsql\DBAs];

CREATE ROLE RoleTest;

ALTER ROLE RoleTest ADD MEMBER [sirsql\DBAs];

ALTER ROLE RoleTest DROP MEMBER [sirsql\DBAs];

This doesn’t just work with database roles. Also gone is the sp_addsrvrolemember syntax (which for some reason had you specify the login first then the role, opposite how sp_addrolemember did).

ALTER SERVER ROLE DiskAdmin ADD MEMBER [sirsql\DBAs];

ALTER SERVER ROLE DiskAdmin DROP MEMBER [sirsql\DBAs];

Managing roles this way makes a lot more sense than trying to do things via stored procedures. In an effort to ensure that old code still works Microsoft have left the old stored procedures in, but deprecated them, so don’t expect them to work in the next release. Anyway, wouldn’t you rather work to retool your code so that your security experience is more unified?

SQL2012 It’s The Small Things–Default Schemas For Groups

If you’re reading this then I’m sure you know all about SQL 2012 which just went RTM and will be available for general release on April 1st. I’m sure you’ve also heard all about some of the big new changes such as AlwaysOn with readable secondaries, ColumnStore indexes, and FileTables. Missed in all the big announcements are some of the smaller things that make a huge difference in the day to day running of SQL Server. I’m going to put together a few posts outlining some of the smaller, but to me equally significant, changes that are in the new version.

Back in the day there was no facility to provide a default schema to a Windows Group, you would actually get an error were you to try:

USE Master;

CREATE LOGIN [sirsql\DBAs] from windows WITH DEFAULT_DATABASE = TempDB;

USE tempdb;

CREATE USER [sirsql\DBAs];

ALTER USER [sirsql\DBAs] with DEFAULT_SCHEMA = dbo;

This script now works in SQL 2012 and defines the default schema

If we check out the properties of the user we can see that the default schema has been set to dbo:

This is a very welcome addition. Prior to SQL 2012 you would have to create a separate login and user for each user in a windows group and then set the default schema for each, that’s a lot of administrative hassle eliminated.

Please Don’t Use Deprecated Data Types

I know that a lot of vendors like to write for the lowest common denominator (i.e. SQL 2000) but really folks it’s gone too far. I’m sick of cracking open vendor code that’s certified for SQL 2008 and seeing things like IMAGE and TEXT data types. Microsoft deprecated these things back when they released SQL 2005 (see http://msdn.microsoft.com/en-US/library/ms143729(v=SQL.90).aspx under Textpointers). Why are you persisting these things six years later?

I bring this up because I’ve come across further egregious usage of these data types in vendor code yet again. The vendor in question? Microsoft.

Yes, that’s right, the folks that deprecated the data type six years ago is still using it to a large extent within the ReportServer and ReportServerTempDB databases that support SQL Server Reporting Services. Seriously Microsoft? Can you please get with the plan and fix this nonsense?

The following query, run against the ReportServer database will show 14 different tables (31 columns) using a variety of NTEXT and IMAGE data types.

select st.name as TableName, t.name as DataType, sc.name as ColumnName 

    from sys.types t 

        inner join sys.columns sc

            on t.system_type_id = sc.system_type_id

        inner join sys.tables st

            on sc.object_id = st.object_id

where 

    t.name in ('image', 'text', 'ntext')

order by 

    st.name, t.name

I have filed a Connect item asking Microsoft to fix this. Please go vote for it at https://connect.microsoft.com/SQLServer/feedback/details/714117/ssrs-using-deprecated-data-types-in-its-databases and help us rid the world of this old stuff.