开发者

can't change the password for a user

I have downloaded database and attached it to my local sql server, however I can't seem to change the password of one the existing user's on the db.

Using the following command:

ALTER LOGIN [NotificationsUser] WITH PASSWORD = 'password';

I get the error:

Msg 15151, Level 16, State 1, Line 1 Cannot alter the login 'NotificationsUser', because it开发者_JS百科 does not exist or you do not have permission.

Is this possible?, what access permissions do I need to change user permissions anyway ?


If you've attached this database to your local SQL server then you'll need to do a couple of things:

  1. If you haven't already done so, create user logins on your SQL server to match the ones that exist in the attached database. It's simpler to do this before attaching the database. But it can be done after the DB has been attached.

  2. Because the SID's of the users in the newly attached database won't be the same as the newly created logins you'll need to resolve this using the sp_change_users_login stored procedure. The database user's are in effect orphaned.

For example if you have:

SQL Login: bob Attached database user: bob

Open a new query in SQL Management Studio for the attached database then run:

sp_change_users_login @action='report'

If you have "orphaned" users in your database then you'll see a result set that looks like:

UserName  UserSID
bob       0x57F6DFA5D5D7374A97769856E7CB5610

To reconnect this user to a SQL login execute:

sp_change_users_login @action='update_one', 
                      @loginname='bob', 
                      @usernamepattern='bob'


I think you're confusing a database user with a server login.

Your database may have a user in it called NotificationUser but this needs to be associated with a server login, which is the object you're trying to alter with the script. A database restore from a different server won't have created this server login so there's a good chance it doesn't exist on your server. More info here

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜