Blog - comments

When I studied Oracle New Feature Guide "Media Failure: PDB SYSTEM Data File" , I was surprised tha...
Hayat Khan

Really a nice article to study.

Thanks,

Amol Bhoite

ana 100 goid

mokok

ana 100 gold

mokok

Bonjour,

Tout d'abord merci pour cet article. J'aimerai savoir si ACFS est gratuit ?

Chris

Chris
Blog Stephane Haby SQL Server 2012: How to create a Server Role?

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server, 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 the 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.

Createuser1

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

 Connection

 

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

ReadingLogsError

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.

AddRole

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

GiveRoleToUser

Step 3: Read the Logs

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

ReadLogFile

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

These two pictures are proof:

CreateDatabase

CreateUser

 

Conclusion

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

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.

Comments

  • 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 Friday, 18 April 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)
NewsOfficesContact

Contact

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