SQL 11–Denali Creating Server Roles

One of the neat new features of the new SQL Server, is the ability to create server roles, this was not possible before, you could only create a role on the database, and grant database level permissions to it. Now you can create a Server Role, and grant permissions to it, this allows us to group permissions and make then more visible, as its really boring to check every login for its server level permissions, so here’s how to use it.

The permissions you need for this are:

• CREATE SERVER ROLE, which is present at sysadmin server role.
• Impersonate if you set ownership for this role.

For a full doc check this link.

Just one note: a Server role cannot contain permissions for database level securables (objects).

I’m going to demonstrate how to create a server role, and add a user to it.
But first I’m going to create a SQL User for demonstration purposes.

CREATE LOGIN TEST
WITH PASSWORD = N'TEST'
,CHECK_EXPIRATION = OFF
,CHECK_POLICY = OFF
GO

Then we get to the feature display:

CREATE SERVER ROLE VIEW_STATE
GO
GRANT VIEW SERVER STATE TO VIEW_STATE
GO
ALTER SERVER ROLE VIEW_STATE
	ADD MEMBER TEST
GO

Note that on the first statement we’re creating the role with the name of SERVER_STATE, then we’re adding the permission VIEW SERVER STATE to the role, and finally adding the user to the role, notice how we add the user to the role, its pretty different that the database role which uses the sp_addrolemember procedure.

Well I hope’s this helps you see some of the goodness that the new SQL 11 will bring us.

Greetings from the
Developer Seer

The New TSQL Code snippets from SQL 11–Denali

I posted the features that I liked most before on my post about where to download the new CTP1 of SQL Denali.

One of them were TSQL Code snippets.

This post will have some screenshots about the new feature, and how to use it.

My opinion on this feature is that it is awesome, and it still needs improvement.

The MS books online of this feature is on this link.

So its Just Open up the new SQL Server Management Studio.

Open a New Query, and press 

Ctrl K + Ctrl X

You will see this window:

Now if you select Function and Create Multi-Statement Table Function

You’ll see this script being added to the window.

Note the highlighted areas, the current area I am editing is the schema, however if I edit for example the parameter name, it will be updated across the script.

It works a little like the Visual Studio .NET Snippets, however for developers the snippets are really more powerful, lets hope this feature gets improved, as the potential for this is Huge.

Well this was fun, hope this gets you excited about what's coming next on the next version of SQL.

Greetings for all,
The Developer Seer.

Error 0x80010105 on SQL 2008 R2 Configuration Manager

Today I faced this problem and wanted to share it, When going to SQL Configuration Manager and clicking on SQL Server Services I was simply presented a error that said
“The server threw an exception. [0x80010105]”
with a little red icon of error, and no refresh would save us.
So I googled it a bit and found this blog post.
http://aspdotnetdevelopment.wordpress.com it’s a blog about ASP.NET, MVC and SQL.
And the owner of the blog says it’s a WMI problem, so go on and restart it’s service.
The solution is on Start –> Run –> services.msc –> Find “Windows Management Instrumentation” –> and Click Restart.
He also points that the solution came from here:
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f7fc2002-169c-42a4-8bdf-2dc4a751f950
So Thanks to WeiLin Qiao and to the above cited blog I’ve solved my problem, and hope it helps you solve yours if you happen to have this problem.
Good Luck and Farewell.
The Developer Seer.

SQL Server 11–Denali CTP 1 is available for download

Few know that the next version of SQL Server that is being developed as we speak already has a first release ready for tests!
Its called Community Technology Preview after all, so we the SQL Server community can test it and send feedback to Microsoft.
So where is it and what's new ?
Download it HERE.
What's New???
Here’s the official list.
Things that caught my attention:

• The new SEQUENCE (know in Oracle)
• A New SQL Way of doing Server Side Pagination
• Now you can create Server Level Roles
• TSQL Code Snippets on Management Studio
• And The new Project Crescent

About how to send feedback is unknown, the only place I know that we can send feedback to Microsoft is the Connect site.
The Developer Seer wish's you a good study.
Greetings,
The Developer Seer.

Find SQL Default Data and Log Directories using TSQL Independent of Instance name or Cluster Installation

 

Hello, I was needing this function for some reason and I though someone else may need it.

So basically this info we are wanting is the default place where SQL will place the Data and Log Files, this will be brought when you try to restore a database using the GUI, if you’re not using the GUI you’ll have to know the directories.

Well SQL just doesn’t have a Custom Function for finding these directories, its not contained on SQL itself, SSMS shows you if you right-click the server you’re connected and select properties, then selecting Database Settings, but other than that the only way to know this info is by looking it up on the windows registry.

So how do we need to look in the registry with TSQL, we also need to check the right place because if you are running a named instance this values will be on a different place that they would be if we were querying a default instance, the same for a clustered instance.

So How do we go through this?

Well there's a procedure called XP_INSTANCE_REGREAD, if we were just reading any key on the registry we would be using xp_regread, but we need this info regardless of instance, so this xp_instance_regread will do the job of localizing which instance we’re currently using.

Now we need to query two registry keys one for the data directory and another for the log, however on cluster installations the Data directory is located on another key, so just using xp_instance_regread won’t save us this time, we’ll have to manually check for this. So on the code I expose bellow I'm checking first on the registry key that is not the one for the cluster and if the value is not there I’ll read the cluster one, that way we’ll get the value always.

The Keys are (remember Instance aware):

For Data: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer
and Value DefaultData

For Log: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer

and Value DefaultLog.

So now here’s the code.

declare @datadir nvarchar(4000)
        ,@logdir nvarchar(4000);

EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultData'
    , @datadir output;

IF @datadir IS NULL
BEGIN
EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\Setup'
    , N'SQLDataRoot'
    , @datadir output;
END
EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultLog'
    , @logdir output;

SELECT @datadir as Data, ISNULL(@logdir,@datadir) as Log;

Hope this helps you!

Kind Regards,
The Developer Seer.

CHECKDB WITH DATA_PURITY

I’ve Learned this recently, about two months ago.

Again on Paul Randal’s blog, click here if you want to see the post.

The thing is SQL 2005 introduced a check on DBCC CHECKDB, that checks for columns values like, is my column values within range of my selected data type?

When migrating databases I’ve seen some negative DateTimes (which Query Analyzer does display, some weird float values that even query analyzer does not display)

The First question you might have is how the hell did this values get in there?
Well… On SQL 2000 and bellow, SQL allowed data Import without some checking with ended up with those values in there.

If you created the database on SQL 2005, this check is always done when you issue a:

DBCC CHECKDB ([db_name])

if your database is from a SQL 2000, you may have to perform a explicit check, and once this check is completed successfully a value is changed in the database boot page, so SQL know that it passed once and will enforce checking every time.

Here’s the code for an explicit check:

DBCC CHECKDB ([db_name]) WITH DATA_PURITY

Remember to change the db_name for your database name, ok?

If you got any problem with the invalid values know you have two choices, and I don’t like any of them.

1. You can delete those rows. (lame solution right).
2. You can update the values for those rows, but probably you won’t be able to see what’s currently stored if you get this problem.

For more information around this you can check this MS KB. 

Generating a Table’s diagram and Script

Great tip for Entity-relationship model and script generation.

A great tool for building Entity-relationship models for databases and it’s scripts

http://ondras.zarovi.cz/sql/demo/

it generates mysql, sqlite, web2py, mssql (Microsoft sql), postgresql, oracle, sqlalchemy, vfp9 scripts for a designed model.

You can select the language in the options, and then start designing the database, or do that in the end when you want to generate the full script, this helps you to create a ERM model and visually see the relationship’s test your ideas and see if they really work.

Creating Tables

You can start by creating tables using the menu Add Table button, you may be tempted to drag a table, but that won’t work, just click the diagram and there will be a dialog asking you the name of the table. The table comes with an default id field, you can edit it, changing it’s type by double clicking it, then selecting it's name, type, size (when applicable), if it is a auto increment field, and if it allows nulls. You can add new fields with the Add field button also in the Menu and create new fields on the desired table.

Creating Relationships

Relationships can be created in two ways in here, first you can create the destiny field in the desired tables and then select which one is part of the key (only keys can be related)  then click Connect foreign key, and the connection between the fields will be created (and also the foreign key). The other way of doing it is by only creating the field on the source table and then selecting the field (must be part of the key),  clicking on the menu Create foreign key and then selecting the table you want to have the key, the field should be created automatically.

Setting a Primary Key

When you select a table you can use the menu to define it’s unique key (a table doesn’t need to have one, but its highly desired), you can click the Keys menu item, there you can define which columns are part of the primary key, moreover you can also define indexes, and which columns are part of the index, however I don’t think you can create an index that includes some columns but are not part of the index.

After that you can select Save/Load and Generate SQL for the script.

Also one nice trick is that you can download this to work locally, click on documentation and Installation then follow the instructions and use.

Here’s a diagram example:

Hope this helps
Good Luck
The Developer Seer.

Refreshing the Intellisense cache in SSMS 2008

I'm currently using alot SQL Server Management Studio (SSMS) 2008, and often when I create a new table it doesn't appear on Intellisense, and after a little research I came across this website:

Source: Dan Jones (aka dtjones) refreshing the intellisense cache

by Dan Jones.

And learned why this happens, and how to fix it.

First how to Fix it: There's two ways, the quicker and what you'll want to know if you are a shortcut Ace (or wannabe) is Ctrl + Shift + R , the other way is to go to the Menu in: 

Edit -> IntelliSense (last option) -> Refresh Local Cache 

Then Why it happens: SSMS reads all this info on what tables exist's and other things and cache it locally (it doesn't check every time), if you create a table or anyone creates a new table on the database SSMS won't update his local cache, so you have to do this manually, a good point of observation is that sometimes SSMS does update the cache and it depends on some actions, to learn how does the SSMS cache work you can observe when does it reads his cache.

Hope it helped,

Good Luck

The Developer Seer.