Blog - comments

Hi goog article can we install avdf firewall with flat network if it's possible please let me know ?

Thilina
First, thank you for your interrest in this blog.Yes, the byte code will be interpreted each time bu...
BIEHLER Stephane
Pretty sure this is wrong:> already said that JVMs interprets the generated byte code - that's true...
Gs
Michael, great article, however, I would disagree on DRS/Host Affinity. You are legally only requir...
David Bradshaw
Hi lauri, db_file_multiblock_read_count is still used in exadata smartscan because it defines the si...
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.

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.


MCSA  MCSE  mvp

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 Saturday, 25 October 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

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