May 26, 2016 at 2:36 am
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. ?
May 26, 2016 at 2:59 am
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
May 26, 2016 at 4:14 am
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?
May 26, 2016 at 4:24 am
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
May 26, 2016 at 5:15 am
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
May 26, 2016 at 5:20 am
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.
May 27, 2016 at 1:09 am
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