Month: July 2011

Custom Server Roles! Yes, this is a brand new enhancement Microsoft has provided in SQL Denali.
Upto SQL Server 2008R2, We only had flexibility for creating Custom Database Roles. But the Server roles were fixed, which comes along with your installation.

So, Let’s peak on how to create a custom Server Role and assign Server Scoped Permissions in Denali!!
Pretty Simple Using GUI:
Scenario: Let’s assume your team hired a Level2 DBA who needs supervision for few weeks/months. You don’t want to grant him sysadmin privileges where he can do what ever he wants. You really don’t want a Jr DBA connecting to your SQL Server and stop/Start/Restart SQL Services. So let’s assume your plan is just to limit his permissions to Create/Alter any Database, Alter Trace – for Running Traces, Alter Logins/Connections/Linked Servers. What can be done with less pain is Just create a Server role with desired permissions and add him/her as its member.( you can use your own imagination for Server Scope Permissions, for example…if you want him/her to control DB Mirroring, consider granting perms on Mirroring ENDPOINTS and so on….). You can Just add all your newly joined DBA’s to this Role which is Just a matter if Single Button click 🙂
In my Example, I’m creating a Server Role called as “Lev2DBA” and choosing Server as a SECURABLE and granting permissions as per my requirement.
Snap1:

Snap 2:

Now, go to Members Tab and Add that Level2 DBA(login “test” in my case) to this Role as shown below.

Note: You can also directly add this to any existing Fixed Server Role directly from “Memberships” tab as shown below.

If you’ve  observed thoroughly,by this time you might’ve noticed that in the above Screenshot there is no “SYSADMIN” role. I believe MSFT is not allowing us to add these members to SYSADMIN role. ( Not sure, why they are allowing ServerAdmin in that case!!!)

It’s also pretty Simple from T-SQL: You can follow the below Syntax for achieving the same.

USE [master]
GO
CREATE SERVER ROLE [Lev2DBA] AUTHORIZATION [sa]
GO
ALTER SERVER ROLE [Lev2DBA] ADD MEMBER [test]
GO
GRANT ALTER ANY CONNECTION TO [Lev2DBA]
GO
GRANT ALTER ANY DATABASE TO [Lev2DBA]
GO
GRANT ALTER ANY LINKED SERVER TO [Lev2DBA]
GO
GRANT ALTER ANY LOGIN TO [Lev2DBA]
GO
GRANT ALTER TRACE TO [Lev2DBA]
GO
GRANT CREATE ANY DATABASE TO [Lev2DBA]
GO

That’s it Guys! Very Very Simple huh?….
Happy Testing Denali!

Contained Database : A Brand new interesting feature introduced by Microsoft in SQL Server Denali(2011).
let’s get straight to the point. Simple and straightforward use of contained Database(s) – Let’s say you’ve multiple applications sharing the same SQL Instance and you’ve multiple Jobs,Linked Servers, Logins for respective Databases. consider the scenario, where you are trying to migrate the database, the entire pain comes when dealing with respective Logins, Jobs,Server Objects. 
With the introduction of “CONTAINED DATABASE”, you can assume that you are isolating your application(database) from rest of the applications(Databases) on this SQL Instance. So…They are independent of Instance itself in terms of Instance level dependencies!
Are they really independent in every aspect?? No….definitely not! let’s say your SQL Instance is DOWN, then of course these Database(s) will also be Down ;-p. So basically we are going to introduce some level of isolation from rest of the Databases and some level of freedom(independence) from actual SQL Server itself!

Terminology :
I’ve taken this terminology(definitions) from MSDN Library. The link is provided below FYI.
http://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx
Application boundary:The boundary between the application model and the instance. Application model functions are within the application boundary, and instance functions are outside the application boundary.
Contained:A user entity that lives entirely within the application boundary.
Uncontained:A user entity that crosses the application boundary.
Non-contained database:A database with containment set to NONE.
Fully contained database:A fully contained database does not allow any objects or functions that cross the application boundary. Fully contained databases are not currently available.
Partially contained database: A partially contained database is a contained database that allows features that cross the application boundary.
Contained user:There are two types of users for contained databases.

• Contained database user with password (SQL Auth)
     Contained database users with passwords are authenticated by the database.
• Windows principals (Windows Auth)
     Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database.

Note: Users based on logins in the master database can be granted access to a contained database, but that would create a dependency on the SQL Server instance, so creating users based on logins is not recommended.

So what exactly are we gaining from this new feature?? It stores the below mentioned thingswhich can make a DBA life much more painless…:)

It stores all the Cool info like Tables,Procs,Functions,Constraints,Views,Schemas..etc as any other regular Database. Besides this, It also stores Instance level objects like Logins, Application Agent Jobs, Error Msgs, Linked Servers etc. Hurrrray….we can escape from dealing with Orphaned Users, SID Mappings blabla…:) Tadaaaaaaaaaaaaa!!

Let’s start a Quick DEMO:

Step 1: By default “Contained Database Authentication” property is set to ‘0’-False as shown below.

We’ve to set this Option to TRUE from GUI or use the below code from T-SQL:

From T-SQL:

CREATE DATABASE [Contained_tst] CONTAINMENT = PARTIAL ON  PRIMARY ( NAME = N’Contained_tst’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’Contained_tst_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)GO

Step3: Now we’ve created a contained Database. Let’s play a bit with creating a test user and connecting via the new test user.

From TSQL:

USE [Contained_tst]GOCREATE USER [contained_usr] WITH PASSWORD=N’test’, DEFAULT_SCHEMA=[dbo]GO

Now let’s try connecting to the SQL Instance using the “contained_usr”.

Note: it might fail if you try to connect to default Master database because this particular login doesn’t exist at the Server level in the Master Database.Once connected through SSMS, It looks like the below Screenshot.

I’m not seeing any other Databases, Agent, Server objects…Literally I’m not able to see anything beyond this particular database. waiting for testing this Brand new thing in real environment!One More GOTCHA :“ALTER DATABASE” Syntax will fail for Contained Databases. We’ve to use “ALTER DATABASE CURRENT“!!Conclusion: IMHO this is a great new feature which we all(at least I was) were waiting since ages! Even though it is well publicized as a Contained Database is instance or server independent, has no external dependencies and has self contained mechanism of authenticating users, I would say no! It’s actually not Server independent. A contained Database can’t escape anything which impacts the Physical Server(Instance SHUT DOWN, SERVER REBOOTS etc).Hope this helps to give a Kick Start for exploring this new added feature of DENALI.