04 February, 2011

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

03 February, 2011

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:

image

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

image

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.

image

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.

02 February, 2011

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:
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.