Blog - comments

For me the bigger issue is in having dedicated storage just for my Oracle environment. We're a fair...
Your instructions are better than the Oracle document. Thanks for writing this blog. It is very help...
Anil K
I read your blog.I like your blog.
machine vision lens

I read your blog. Thanks for sharing your blog.

Machine Vision Lenses
I have read so many articles or reviews on the topic of the blogger lovers but this piece of writing...
Blog Stephane Haby SQL Server 2012: How to create a Server Role?

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

SQL Server 2012: How to create a Server Role?

Recently, during one of my missions, a client asked me to give him access rights to the SQL Server logs from SSMS to his application managers. As you know, you must grant the role “securityadmin” to have such rights. However, with this role you can also manage logins, CREATE DATABASEs and change passwords.

These are “too many rights” for an IT manager, but there seems to be no other wayCry ... or is there?

Of course, SQL Server 2012 can resolve this problem! How?

  •  Since SQL Server 2012, you can create your own server roles.

Step 1: Create a user for my demo

I can create a simple SQL server user with the right to be owner of the famous AdventureWorks Database.


I can connect to SSMS (SQL Server Management Studio) with this account.



Let's try to look up the SQL Server Logs...


In the error message, we can read: 

Only members of the securityadmin role can execute this stored procedure” Error 15003

Naturally, the easiest way is to give “securityadmin” role to my user Appli_Responsable, but I don't want this user to be able to create new databases or new users.
My new choice is to do my own role to read the logsCool

Step 2: Create a new server role

Connect to your instance with sysadmin rights and then you can use the wizard or type directly in a query.

In my example, I created the server role “ReadingSQLServerLogs”

I then granted the “VIEW SERVER STATE” to my role and the “CONNECT” by default.

These rights are sufficient to read the logs.


Now, we can grant this role to the user Appli_Responsable like this:


Step 3: Read the Logs

Let's connect my user and…hohoho…Smile...I can read logs!


To finish my demo, I tried to create a database and a user and I cannot … not enough rights!

These two pictures are proof:





I just wanted to present this new simple and useful feature to you. I hope this gives you some good ideas for the security of your databases in the future!

Rate this blog entry:

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.



  • Guest
    Seryozha Hovhannisyan Thursday, 05 April 2012

    I'm just stydiing sql but it already interest me...
    Where can I download any free version??
    Will be very glad if which of you help me and stude me sql !!!!)))))

Leave your comment

Guest Wednesday, 29 July 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter