How to include sp_change_users_login in my program ?

  • Hi, all, my case is:

    My program uses login: userabc to execute:

    ... ... ...

    restore database dbabc

    sp_change_users_login 'auto_fix', 'userabc'

    MERGE ... AS T USING ....

    ... ... ...

    However, userabc doesn't have admin right, so it can't run the sp_change_users_login for itself, thus, my question is:

    Is it possible, in run time mode, to run sp_change_users_login as a SQL user who has the admin right, eg. sa. ?

  • If userabc has just restored the database, then it's going to be the database owner (dbo), so you shouldn't need to run sp_change_users_login. Unless I've missed something, that is. On a more general note, you should use ALTER USER instead of the stored procedure, which is deprecated.

    John

  • I have to run sp_change_users_login, otherwise, I can't add the db_owner right to the user: "user already existed ...."

    Can I login as sysadmin within my program ?

    Or how can I run a SQL stored procedure which needs sysadmin right to do within my program ?

    Do you understand what I mean?

  • onlo (5/26/2016)


    I have to run sp_change_users_login, otherwise, I can't add the db_owner right to the user: "user already existed ...."

    But the user is dbo already, so you don't need to make it a member of db_owner. And even if you did, you should still use ALTER USER.

    Can I login as sysadmin within my program ?

    No, sysadmin is a server role, not a login. If you mean sa, yes, so long as you have the password. It's not recommended from a security point of view, though. It's better for each DBA to have his or her own login to the server, preferably a Windows login.

    Or how can I run a SQL stored procedure which needs sysadmin right to do within my program ?

    Use a login that's a member of sysadmin to connect to SQL Server. But you don't need to do this. You already have sufficient privileges to restore the database, and that in turn makes you dbo on the restored database, meaning you can do whatever you like at a database level.

    John

  • I've found the best way to resolve this issue is to sync the sids.

    SQL users generate a unique Id that is different on each login...so My login[Bob] is not the same as your login[Bob], even though they have the same name.

    for example the login [AppUser] on production has a security identifier is = 0x2F4952FB12211C4D9D5C75C0B3C57C6A, on one server, and

    a loginby the same name, [AppUser] on anotherserver is =0xA890AAF8DE1BA345A847E8C487F931BB

    that's why they become orphaned.

    grab the procedure sp_help_revlogin from Microsoft:

    https://support.microsoft.com/en-us/kb/918992

    run that on production, and copy the code for your one specific login.

    drop the login on your other server(test?)

    run the script to add the loginback.

    the login will forever more be in sync, and you will never have a problem with that login again.

    users in databases will automatically point to the right login.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • John Mitchell-245523 (5/26/2016)


    If userabc has just restored the database, then it's going to be the database owner (dbo), so you shouldn't need to run sp_change_users_login. Unless I've missed something, that is. On a more general note, you should use ALTER USER instead of the stored procedure, which is deprecated.

    John

    You can use EXECUTE AS in your SP if that is what you are looking for. But as john has rightly you already have access since you just restored it, and if it is for a different user you can you ALTER USER command to change the login associated (Unless you are using SQL Server 2000). Either way sysadmin is way too high privilege we are considering for this requirement.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks all of you.

    Yes, the user restored the db, of course, he also has the right to do SQL Login and SQL User mapping.

    I will use ALTER USER instead and will not use sp_change_users_login from now on.

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

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