Create Custom SQL Server Role in SQL 2008 Server

Question

I want to add new custom SQL Server Role and assign the new users to these appropriate roles respectively. I try to find how to create role, but I could not find the place to add that. Could you please guide me how to achieve my requirement?

Do you mean in a database or at the server level? SPE109 has a good point, I and marc_s may have mis-understood — gbn
– gbn, Commented Jun 2, 2011 at 10:23

marc_s · Accepted Answer · 2011-06-02 09:59:08Z

7

You cannot do this in the 2008 version just yet - this is a new feature that you'll get with SQL Server 2011 (a.k.a. "Denali") sometime in 2011/2012.

See some resources:

answered Jun 2, 2011 at 9:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gbn · Accepted Answer · 2011-06-02 10:14:41Z

4

You'd simply GRANT the rights you want to a login rather than a role pre SQL Server 2011. Not ideal of course but it works.

We use this to enable app teams to see what is going on without hassling the DBAs.

For example,

CREATE LOGIN [MyDOmain\FolkITrustGroup] FROM WINDOWS;
GO
GRANT VIEW SERVER STATE TO  [MyDOmain\FolkITrustGroup]
GRANT VIEW ANY DEFINITION TO  [MyDOmain\FolkITrustGroup]
GO

Edit: This achieves your requirement without using server roles which don't exist yet...

edited Jun 2, 2011 at 10:14

answered Jun 2, 2011 at 10:04

gbn

435k85 gold badges602 silver badges690 bronze badges

Comments

SPE109 · Accepted Answer · 2011-06-02 10:03:30Z

0

You can create Database level roles see Create Role and the links on the page for more detail.

answered Jun 2, 2011 at 10:03

SPE109

2,9511 gold badge19 silver badges16 bronze badges

2 Comments

gbn Over a year ago

This isn't a server role as OP asked

SPE109 Over a year ago

Did they? It's not clear to me whether he asked about creating a server level or database level role, they only asked about creating a role in SQL Server.

mihsathe · Accepted Answer · 2011-06-02 10:11:08Z

0

Well I don't have SQL server installed right now so I can't check. But my knowledge of SQL tells me :

creating a role :

create role <rolename>;

granting privilages :

grant <privilages> on <relation> to <rolename>;

revoking privilages :

revoke <priv> on <rel> from <rolename>;

grant role to user :

grant <rolename> to user;

You can also create the hierarchy of it : e.g.>

create role supervisor;
grant select, insert on db.table to supervisor;
create role manager;
grant supervisor to manager;

grant manager to John;

This is the standard SQL way. It is not available yet in current versions of some DBMS. Not sure about SQL server.

answered Jun 2, 2011 at 10:11

mihsathe

9,19412 gold badges43 silver badges55 bronze badges

1 Comment

mpiliszcz Over a year ago

The last statement is wrong. It should be: EXEC sp_addrolemember 'manager', 'John'

Sha Beer · Accepted Answer · 2013-09-30 02:08:38Z

-1

****A. Creating a server role that is owned by a login****


****The following example creates the server role buyers that is owned by login BenMiller.****

USE master;
CREATE SERVER ROLE buyers AUTHORIZATION BenMiller;
GO


****B. Creating a server role that is owned by a fixed server role****


****The following example creates the server role auditors that is owned the securityadmin fixed server role.****

USE master;
CREATE SERVER ROLE auditors AUTHORIZATION securityadmin;
GO

answered Sep 30, 2013 at 2:08

Sha Beer

273 silver badges9 bronze badges

Collectives™ on Stack Overflow

Create Custom SQL Server Role in SQL 2008 Server

5 Answers 5

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related