Post reply

Role

  • May 11, 2015 at 8:35 am

    #318086

    I need a script for to create a role to cover SP creation/execution, any table data manipulation (insert, update, delete & select) so that the controlled access can be given to a user instead of DBO access.

    This should be across all DB's in that server.

    Any help here pls.

    thanks in advance.

  • May 11, 2015 at 8:49 am

    #1796970

    i created below script, but i need to run this script in all dbs at once...

    any suggessions here.

    CREATE ROLE [SupportRole]

    Go

    GRANT EXECUTE ON SCHEMA ::dbo TO [SupportRole]

    GRANT SELECT ON SCHEMA ::dbo TO [SupportRole]

    GRANT SELECT, INSERT, DELETE, UPDATE SCHEMA ::dbo TO [SupportRole]

    GO

    EXEC sp_addrolemember N'SupportRole', N'SupportUser'

    GO

  • May 11, 2015 at 9:23 am

    #1796981

    I am facing some syntax errors.Any suggestions here pls.

    Error : "Msg 102, Level 15, State 1, Line 21

    Incorrect syntax near 'SupportRole'. ".

    Below is the script .

    --=================================================================

    ---Create role in all dbs

    DECLARE @command nvarchar(max)

    SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')

    BEGIN USE ? EXEC(''CREATE ROLE [SupportRole]

    Go

    GRANT EXECUTE ON SCHEMA ::dbo TO [SupportRole]

    GRANT SELECT ON SCHEMA ::dbo TO [SupportRole]

    GRANT INSERT ON SCHEMA ::dbo TO [SupportRole]

    GRANT UPDATE ON SCHEMA ::dbo TO [SupportRole]

    GRANT DELETE ON SCHEMA ::dbo TO [SupportRole]

    GO

    '')

    END'

    EXEC sp_MSforeachdb @command

    ---Add role to user

    DECLARE @command1 nvarchar(max)

    SELECT @command1 = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')

    BEGIN USE ? EXEC sp_addrolemember 'SupportRole', 'SupportUser';

    END'

    EXEC sp_MSforeachdb @command1

    --=========================================================================

  • May 11, 2015 at 9:34 am

    #1796993

    GO isn't a T-SQL command - it's a batch separator. It's recognised by SSMS but not by many other providers. When you use EXEC('...'), you're not running your command in SSMS, hence the syntax error. Try removing the GOs and see what happens.

    John

  • May 11, 2015 at 10:03 am

    #1797010

    yes, i tried removing 'GO' statements, still getting same syantax error.

  • May 11, 2015 at 10:07 am

    #1797012

    You need to escape the quotes around SupportRole and SupportUser as you did around the database names.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 11, 2015 at 10:10 am

    #1797013

    actual syntax error in below script part.

    ---Add role to user

    DECLARE @command1 nvarchar(max)

    SELECT @command1 = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')

    BEGIN USE ? EXEC sp_addrolemember 'SupportRole', 'SupportUser';

    END'

    EXEC sp_MSforeachdb @command1

  • May 11, 2015 at 10:11 am

    #1797014

    Thanks Gila!!

    Its fine now.

Viewing 8 posts - 1 through 7 (of 7 total)

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