Solved

How to map windows accounts ?

Posted on 2008-10-24
7
519 Views
Last Modified: 2008-12-06
I want to transfer a database between server A and server B.

On server A I have some sql and windows accounts with user mappings to that database.

I may use the store procedure sp_change_users_login to map sql account but not to map windows accounts.

How map windows accounts ?
0
Comment
Question by:LuisGavinhos
  • 3
  • 2
7 Comments
 
LVL 4

Accepted Solution

by:
ThorSG1 earned 500 total points
ID: 22803826
All of these will work for both domain accounts or sql accounts.  For domain accounts use this format: Domain\username
If you end up with orphaned accounts from Server A  use this:
EXEC sp_revokedbaccess 'username'
This grants access to the database in question.
EXEC sp_grantdbaccess 'username', 'username'
This grants the priviledge level you want to use.

EXEC sp_addrolemember 'db_datareader', 'username'

You can check help for each of these to verify the formating.
0
 

Author Comment

by:LuisGavinhos
ID: 22819800

The sp  sp_change_users_login  at Windows 2005 no longer support mapping windows logins like XXX\XXX. You may read at book online at this sp : "sp_change_users_login cannot be used with windows logins"

I tried and receive the following error : "Terminating this procedure. The User name 'PDGRN\sqladmin' is absent or invalid.

Any idears ?
0
 
LVL 4

Expert Comment

by:ThorSG1
ID: 22822796
I'm not saying to use that stored procedure.
Microsoft suggests not using the sp_grantdbaccess as it will not be supported in future versions.  It will be replaced with the command create user.
So weather you use the sp_granddbaccess or create user those are what you need to use.  You are correct the stored procudure sp_change_users_login will not work for windows accounts.  Both will work with SQL 2005.
Once you use one of the above options I've suggested you can then run the stored procedure sp_addrolemember.  This will grant the user the permissions you want to give them.
Examples:
-- First example is a domain account.  Second example is a sql account.
exec sp_grantdbaccess 'microsoft\jimbob', 'microsoft\jimbob'
exec sp_grantdbaccess 'jimbob', 'jimbob'
--
exec sp_addrolemember 'db_dataowner', 'microsoft\jimbob'
exec sp_addrolemember 'db_datareader', microsoft\jimbob'
exec sp_addrolemember 'db_datareader', 'jimbob'
0
 
LVL 4

Expert Comment

by:ThorSG1
ID: 22907623
Have you had any luck with this issue?
0
 

Author Comment

by:LuisGavinhos
ID: 22916610
I created the accounts manualy and run sp_change_users_login.
Finaly I had no need to create windows accounts.

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query 12 67
Stored Procedure error 45 48
SSIS how to COMPARE a data column from different servers? 6 89
Inserting a column in a table that creates an ID and row number 4 53
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now