Post reply

user get created as disabled when using exec sp_addrolemember

  • February 11, 2014 at 8:38 pm

    #282272

    Hi All,

    I have a project that drops and recreates the databases on each release. Of course this removes all database level security. I have created a script to save me manually having to reapply but when I run it the users get created but they are disabled (red down Arrow) Any idea why ?

    I'm using SQL 2012 SP1 Developer

    Script =

    use DatabaseName

    go

    exec sp_addrolemember db_datareader, [domain\user]

    exec sp_addrolemember db_datareader, [domain\user]

    go

    Thanks

    Corinne

  • February 11, 2014 at 9:28 pm

    #1688205

    So you shared the portion of the script to add a login to a role. But what about the portion to grant the login access to the database?

    When you drop the database, do you drop the user and the login too?

    When you recreate the database, do you create the login and the database user?

    Or are you just creating the database, creating a database user and then adding that user to the roles?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 11, 2014 at 10:10 pm

    #1688208

    Hi Jason,

    That's what I'd missed

    I was granting a role to a user on a database that didn't actually exist in the database, but did have a login to the instance.

    Alter the script to add the below for each user before executing the sp_addrolemember command

    create user [domain\user] for LOGIN [domain\user] WITH DEFAULT_SCHEMA=[dbo]

    Then it works a treat.

    Many Thanks

    Corinne

  • February 12, 2014 at 7:40 am

    #1688346

    Cool beans. Glad it is straightened out.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

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