CODEWITHSUNDEEP
securitysql-server-2008
TTCG
TTCG

Reputation: 9113

Create Custom SQL Server Role in SQL 2008 Server

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?

Upvotes: 5

Views: 17770

Answers (5)

Sha Beer
Sha Beer

Reputation: 27

****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

Upvotes: -1

gbn
gbn

Reputation: 432261

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...

Upvotes: 4

mihsathe
mihsathe

Reputation: 9154

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.

Upvotes: 0

SPE109
SPE109

Reputation: 2951

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

Upvotes: 0

marc_s
marc_s

Reputation: 754478

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:

Upvotes: 7

Related Questions