How to create a server level role in 2005

  • 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

  • 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

  • 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?

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

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

  • 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

  • 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://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/

  • 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

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

  • 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

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

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

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

  • 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]

  • 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