Associate User to Login - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Associate User to Login

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-09-13 : 19:05:08

Hello,

I am creating a login and then associating an existing user to that login and also to a role:


-- Define login
create login [$(DatabaseName)]
with password = '@Password', 
check_policy = off,
check_expiration = off, 
default_database = [$(DatabaseName)],
default_language = english;

-- Associate user
exec sp_change_users_login 'Update_One', N'FOP', '$(DatabaseName)';

-- Define role
grant select, insert, update, delete, execute on schema::dbo to Application;
exec sp_addrolemember @membername = N'FOP', @rolename = N'Application'

What ever I do I get the following error:
Procedure sp_change_users_login, Line 114 Terminating this procedure. The User name 'FOP' is absent or invalid.

On this code line:


exec sp_change_users_login 'Update_One', N'FOP', '$(DatabaseName)';

How can the user be invalid if even after this code line I am able to associate it with the role with no problem.

Could someone please tell me what is going on?

Thanks,
Miguel

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-13 : 20:43:11

[code][$(DatabaseName)][/code]
is not the same as
[code]'$(DatabaseName)'[/code]

also, should use ALTER USER (http://msdn.microsoft.com/en-us/library/ms176060.aspx)

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-09-14 : 07:12:41

Yes,

I tried Alter User to. And I have been changing from [$(DatabaseName)] to '$(DatabaseName)' and vice versa and it does not compile or I get errors when running.

For example, now I get:
1 - The server principal 'FOP' already exists.
2 - The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

And my code is (This was ran first):


CREATE USER [FOP]
	WITHOUT LOGIN
	WITH DEFAULT_SCHEMA = dbo;

And then I have the rest:


-- Delete login
if exists (select * from sys.server_principals where name = N'[$(DatabaseName)]')
drop login [$(DatabaseName)]

-- Create login
create login [$(DatabaseName)]
with password = 'Password', 
check_policy = off,
check_expiration = off, 
default_database = [$(DatabaseName)],
default_language = english;

-- Associate user to login
ALTER USER [FOP]
  WITH LOGIN = [$(DatabaseName)];

-- Grant permissions to role and associate user to it
grant select, insert, update, delete, execute on schema::dbo to Application;
exec sp_addrolemember @membername = N'FOP', @rolename = N'Application'

Whatever I try I keep having errors.

Could someone point me what I am doing wrong?

Thanks,
Miguel

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-14 : 11:30:42

are you executing this in SSMS or via a PHP script or something?

$(DatabaseName) looks like a PHP variable to me.

zoltix
Starting Member

1 Post

Posted - 2011-02-14 : 10:18:45

Did you find a solution ? Because I have same error ....

and you repeat this error with this code


CREATE USER TEST_USER WITHOUT LOGIN WITH DEFAULT_SCHEMA=TEST_USER
GO

CREATE LOGIN TEST_USER WITH PASSWORD=N'__qsdfqsdfqsdfqsdf_', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC sp_change_users_login 'Update_One', 'TEST_USER', 'TEST_USER';

Go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources