Post reply

How to create a server level role in 2005

  • July 16, 2008 at 10:48 am

    #122562

    Hi All,

    Does anyone have any idea about how to create a server level role (here the server level means to all the databases - instead to create a role on each database)

    Basically the requirement is:

    User wants a read access on all the user databases.

    I have given the db_datareader permission for all the DBs to that particular user (on windows auth)

    Now he can see the tables but not the stored procedures.

    There are no of SPs on each DB, so instead of giving individual permission of each SP. I am planning to create a ROLE.

    Could anyone please let me know and explain the easiest way - Server Roles according to me 🙂

    Cheers,

    DKG

  • July 16, 2008 at 10:58 am

    #843947

    You can grand execute rights on every proc in a database, but I don't know how to grant execute rights to every proc in every database on a server, except by doing it one database at a time.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 16, 2008 at 12:06 pm

    #843979

    isn’t it a good idea to create a role instead of give permission on each object, because if another user comes with the same requirement, i m goona add him into this role otherwise i have to do this exercise again an again?

  • July 16, 2008 at 12:11 pm

    #843982

    DKG (7/16/2008)

    isn’t it a good idea to create a role instead of give permission on each object, because if another user comes with the same requirement, i m goona add him into this role otherwise i have to do this exercise again an again?

    Roles are definitely the way to go, but you still need to create the roles in each database. There is not a way to create a server level role.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 16, 2008 at 12:15 pm

    #843988

    Do you have any idea or any good link which will help to create such type of ROLEs, i have never worked on ROLES.

  • July 16, 2008 at 12:24 pm

    #843993

    DKG (7/16/2008)

    isn’t it a good idea to create a role instead of give permission on each object, because if another user comes with the same requirement, i m goona add him into this role otherwise i have to do this exercise again an again?

    Yes, do create a role. But you'll still have to give it permissions on each database, not on the whole server. Unless you want to give it something like sysadmin permissions, which would be a bad idea.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 16, 2008 at 12:26 pm

    #843997

    BOL is always a good place to start. Here is an article from this site that is from SQL 2000, but the concepts and process are basically the same in SQL 2005, http://qa.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 16, 2008 at 3:00 pm

    #844081

    To create a role:

    Use database;

    Create Role rolename Authorization owner;

    Grant execute rights to the stored procedures:

    Grant Execute On Schema::schema To rolename;

    If all of your stored procedures are in the dbo schema, then you would have the following:

    Use database;

    Create Role MyUserRole Authorization dbo;

    Grant Execute on Schema::dbo To MyUserRole;

    Now you can add the user to the role with:

    sp_addrolemember @rolename = 'MyUserRole', @membername = 'security_account';

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 16, 2008 at 3:20 pm

    #844084

    Is ther any way i can add all the stored procedure of database into the ROLE - in one go?

  • July 16, 2008 at 4:57 pm

    #844098

    DKG (7/16/2008)

    Is ther any way i can add all the stored procedure of database into the ROLE - in one go?

    The following command grants access to ALL stored procedures in the specified schema:

    GRANT EXECUTE ON SCHEMA::dbo TO role;

    So, any stored procedure in the 'dbo' schema. If you create a new stored procedure in the 'dbo' schema, the role has access to execute that without having to explicitly grant access to that procedure.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 17, 2008 at 3:48 am

    #844231

    There are below permissions i have found in stored procedure for the user(login):

    Alter

    Control

    Execute

    Take ownership

    View definition

    When I have given "View definition" permission to a login, he was able to see the SPs - That is what i want.

    can same be given through ROLE.

    What i did till now is:

    1.) Created a ROLE::

    Use testDB;

    Create Role ViewSPs Authorization dbo;

    2.) Created a login::

    test - with db_datareader permission on testDB database

    3.) Added this user to above ROLE::

    sp_addrolemember @rolename = 'ViewSPs', @membername = 'test';

    Now what would be the next step to provide "View definition" permission for all the SPs on this ROLE or login id - i think on ROLE.

    Thanks in advance.

  • July 17, 2008 at 4:20 am

    #844244

    You also required to set the DENY permissions on all stored procedures which will prevent modifying stored procedure ( if stored procedure definition is viewable to user ).

  • July 17, 2008 at 4:32 am

    #844253

    but before that i want to know how to give "View definition" rights to ROLE...

  • July 17, 2008 at 7:14 am

    #844362

    The syntax is:

    Grant View Definition on [object] to [role]

    or for a schema (will give rights to every object in the schema):

    GRANT VIEW DEFINITION ON SCHEMA::[schema_name] TO [role]

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 18, 2008 at 7:59 am

    #845055

    at last its done!!!!!! :):):)

    In below three simple steps.......

    1.) Create Role ViewSPs --create role ROLENAME(choose the database in which you want to create role)

    2.) Grant View Definition to ViewSPs

    3.) add user to this role

    --go to role,add role memer in general tab...choose the user to whome you want to give permission

    At the end thanks a lot for all for your support.

    Cheers,

    DKG

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply