locked
Need to give permission RRS feed

  • Question

  • Hi,

    I had a request like below

    Need permisison for user R544767

    create/drop/exec - Procedures.
    create/drop/modify - Tables.
    create/drop/modify - Views.
    create/drop/modify - Functions.

    Please help

    Thanks


    Shashikala

    Tuesday, April 22, 2014 6:25 AM

Answers

  • Database : Legal
    Schemas : st0, wrk

    create/drop/exec - Procedures.
    GRANT CREATE,SELECT, UPDATE, DELETE, INSERT on st0::dbo TO R544767

    GRANT EXEC ON st0::dbo TO R544767

    GRANT CREATE,SELECT, UPDATE, DELETE, INSERT on wrk::dbo TO R544767

    GRANT EXEC ON wrk::dbo TO R544767

    It will work?


    Shashikala

    For creating tables, views and procedures.. you should grant like below:

    use [DB_Name]
    GO
    GRANT CREATE PROCEDURE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT CREATE TABLE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT CREATE VIEW TO [User_Name]
    GO

    If you want grant, delete, update, execute permissions like below:

    use [DB_Name]
    GO
    GRANT DELETE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT EXECUTE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT UPDATE TO [User_Name]
    GO

    If you want to grant select and Insert statement:

    use [DB_Name]
    GO
    GRANT INSERT TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT SELECT TO [User_Name]
    GO

    Note:** all the statement will provide access on complete database, if you want to filter by objects you need to look on further.

    Object level permissions will looks like below:

    use [DB_Name]
    GO
    GRANT <<Permission_type>> ON [SchemaName].[ObjectName] TO [User_Name]
    GO

    Example for select:
    use [DB_Name]
    GO
    GRANT SELECT ON [SchemaName].[ObjectName] TO [User_Name]

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Tuesday, April 22, 2014 11:11 AM

All replies

  • Same schema?

    GRANT CREATE,SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO username

    GRANT EXEC ON SCHEMA::dbo TO username-- Grant perpmission on all procedures in
                                          the dbo schema


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, April 22, 2014 7:09 AM
  • Database : Legal
    Schemas : st0, wrk

    create/drop/exec - Procedures.
    GRANT CREATE,SELECT, UPDATE, DELETE, INSERT on st0::dbo TO R544767

    GRANT EXEC ON st0::dbo TO R544767

    GRANT CREATE,SELECT, UPDATE, DELETE, INSERT on wrk::dbo TO R544767

    GRANT EXEC ON wrk::dbo TO R544767

    It will work?


    Shashikala

    Tuesday, April 22, 2014 7:18 AM
  • saying permission of  creating table , the account  need  be involved into the db_owner role.
    Tuesday, April 22, 2014 7:34 AM
  • Database : Legal
    Schemas : st0, wrk

    create/drop/exec - Procedures.
    GRANT CREATE,SELECT, UPDATE, DELETE, INSERT on st0::dbo TO R544767

    GRANT EXEC ON st0::dbo TO R544767

    GRANT CREATE,SELECT, UPDATE, DELETE, INSERT on wrk::dbo TO R544767

    GRANT EXEC ON wrk::dbo TO R544767

    It will work?


    Shashikala

    For creating tables, views and procedures.. you should grant like below:

    use [DB_Name]
    GO
    GRANT CREATE PROCEDURE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT CREATE TABLE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT CREATE VIEW TO [User_Name]
    GO

    If you want grant, delete, update, execute permissions like below:

    use [DB_Name]
    GO
    GRANT DELETE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT EXECUTE TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT UPDATE TO [User_Name]
    GO

    If you want to grant select and Insert statement:

    use [DB_Name]
    GO
    GRANT INSERT TO [User_Name]
    GO
    use [DB_Name]
    GO
    GRANT SELECT TO [User_Name]
    GO

    Note:** all the statement will provide access on complete database, if you want to filter by objects you need to look on further.

    Object level permissions will looks like below:

    use [DB_Name]
    GO
    GRANT <<Permission_type>> ON [SchemaName].[ObjectName] TO [User_Name]
    GO

    Example for select:
    use [DB_Name]
    GO
    GRANT SELECT ON [SchemaName].[ObjectName] TO [User_Name]

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Tuesday, April 22, 2014 11:11 AM