Tag Archives: Security and protection

Implementing user-defined Server Roles in SQL Server 2012

In SQL Server 2012 you can now create an user-defined server role and configure server level permissions for it. In previous versions this was not possible. If we had to delegate someone with administrative tasks we had no choice but to assign more rights and access than required. With SQL Server 2012, user-defined server roles can be created and configured with specific permissions for specific set of DBA’s.

Let us understand with an example how we can create an user defined server role.
Step 1: Right click on Server roles and select ‘New Server Role
udr1Step 2-> As the dialog box opens, type in a server role name -> set the owner to a preferred login. In our case we would choose sa.
udr2Step 3 -> Choose an option\s from Securables window. In our case we chose Servers. Under servers you will find the name of the server. Select the option. Below in the permissions window select the following as shown in the snapshot.
udr3Step 4 -> Click on OK. You will find the new server role under the server roles in SSMS
udr4Step 5 -> Now let us add a login to this new role. Right click on the ServerRole1 -> Click on Properties -> On the members tab click on Add
udr5udr6Step 6 -> Add a login that you want a to give membership to this role. Click on OK.
udr7So now you have successfully given a particular login few administrative rights that is required rather than granting it a privilege like sysadmin.However, one limitation of the user-defined server roles is that they cannot be granted permission on database level securables. Below is the script for the entire action we did.

USE [master]
GO
CREATE SERVER ROLE [ServerRole1]
AUTHORIZATION [sa]
GO
use [master]
GO
GRANT ALTER SERVER STATE TO [ServerRole1]
GO
use [master]
GO
GRANT ALTER TRACE TO [ServerRole1]
GO
use [master]
GO
GRANT CONNECT SQL TO [ServerRole1]
GO
ALTER SERVER ROLE [ServerRole1]
ADD MEMBER [testdb]
GO
Advertisements

SQL Server 2008 – How to troubleshoot error : Granted or revoked privilege EXECUTE is not compatible with object.

Today while executing the following code i got this particular error message:

GRANT EXECUTE ON dbo.fn_SomeFunction_TVF TO SomeRole

Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

I started to read about this error message and this is what i concluded.

You cannot grant execute permissions on Table-valued functions. This is the way this has been designed. Though i could not find as to why this is so.

Solution:
I had to use the references keyword. The syntax for the grant statement hence becomes:
GRANT REFERENCES ON dbo.fn_SomeFunction_TVF TO SomeRole.

A snapshot of the MSDN article that details out permission levels to different securables is below.

Grant

For more details please visit the msdn link :
http://msdn.microsoft.com/en-us/library/ms191291.aspx

%d bloggers like this: