User SA cant mapp to database - 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

User SA cant mapp to database

Author

Topic

amirullail
Starting Member

11 Posts

Posted - 2008-11-11 : 22:23:12

Hi All out there

I'm really new to sql server and now i want to learn more about this sql server 2005

Now i have 4 database that i copy from another server, by offline that server and i copy all the mdf and ldf file (sql server 2000)

Then by using attach technique, i attached all 4 database to my new server with sql server 2005

I can see under SA user from sql server 2000, all database including this 4 database are mapping to this SA user BUT when i looked under properties for this SA user (under sql server 2005), i noticed only master, model, msdb and tempdb are mapped to this login

So when i try to mapped another 4 new database to this SA user, i got this following errors :

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for User 'sa'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15405&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Can anybody help me how to mapped another database that i just attached to this SA user under sql server 2005??

Appreciate if i can get step by step instructions..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-11 : 22:59:42

You will have to map database users to login with sp_change_users_login 'update_one' after you restore. But System admin (sa) should have rights anything in SQL Server level.

amirullail
Starting Member

11 Posts

Posted - 2008-11-12 : 00:04:20

quote:
Originally posted by sodeep

You will have to map database users to login with sp_change_users_login 'update_one' after you restore. But System admin (sa) should have rights anything in SQL Server level.

TQ Sodeep for replying
kindly can u navigate me on how to map database users to login with bla bla bla....

Do i need to login using SA or using Window authentication mode??

amirullail
Starting Member

11 Posts

Posted - 2008-11-12 : 00:28:54

Hi Sodeep

Seem i have no idea on how to execute those line.
Kindly can u assist me on how to achive that.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-12 : 06:56:41

Did you check books online for sp_change_users_login? It is explained in details. It is good to search and practice so you will learn quickly.

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-12 : 09:22:23

if u are not able to connect to DB server then there is no option to know about the credentials.
or if u are able to connect to DB server then u can find the other users in the DB:

select * from sys.sql_logins

malay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources