Re: users with no logins

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 05/30/02 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Thu, 30 May 2002 08:09:49 -0500

User login mapping can get out-of-sync following a restore. You can
correct the dbo user with sp_changedbowner. In some cases you may get
an error stating 'the proposed owner is already a user in the database'
and the script below will work around that problem.

    USE MyDatabase
    EXEC sp_addlogin 'TempLogin'
    EXEC sp_changedbowner 'TempLogin'
    EXEC sp_changedbowner 'sa'
    EXEC sp_droplogin 'TempLogin'

For non-dbo users, use sp_change_users_login. See the Books Online for
details.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Brian Nall" <not@innewsgroups.com> wrote in message
news:#6oVoSyBCHA.1588@tkmsftngp02...
> Is there a reason for database user names to not show a login name
when you
> view the users in a database? I have noticed that in just about all
of my
> databases, the dbo user name shows "sa" as the login. But I have
found that
> one of my databases that show the dbo user name with no login
associated
> with it.
>
> Any ideas,
>
> Thanks in advance for any help.
>
> Brian Nall
>
>
>

Relevant Pages

  • Re: Simple SQL Security Question From SQL Newbie
    ... >exec sp_grantdbaccess 'WIN2K\ASPNET' ... >VS.NET, I can use the server explorer and I can see the database, it's ... >login 'WebCalendar'. ... I also did a GRANT ALL on y2003 TO ASPNET ...
    (microsoft.public.sqlserver.security)
  • Re: dbo association
    ... looked and dbo is the sa login in the master database. ... Well anyway seemes to work for now (I hope sql errors ... >mapping for the dbo user. ... >EXEC sp_changedbowner 'MyLogin' ...
    (microsoft.public.sqlserver.security)
  • Re: Import Logins from text or spreadsheet
    ... set psw to generic value ... add user to database ... EXEC sp_addrolemember 'SomeRole', 'SomeLogin' ... Let's assume your Excel spreadsheet has 3 columns: Login, ...
    (microsoft.public.sqlserver.server)
  • Re: Import Logins from text or spreadsheet
    ... Good suggestion, Steve. ... EXEC sp_addlogin $, $, $(Database) ... EXEC sp_addrolemember $, $(Login) ...
    (microsoft.public.sqlserver.server)
  • Re: dbo association
    ... The dbo user is a special case; database ownership determines the login ... EXEC sp_changedbowner 'MyLogin' ...
    (microsoft.public.sqlserver.security)