Login
-
Hi all,
Our organisation has a ERP Pacakge Microsoft Navision Axapta.This software create a login named BMSSA in sql server.
Yesterday one of the programmer changed the table owner from dbo to bmssa.Now when he was trying to change it back to dbo.It was giving error table does not exists.I solved the problem but when i looked in the login peoperties i found the bmssa have no privilages and no database was assigned.
I want to know when no database is assigned to the login and have no permissons set then how can be that login be the owner of the table of that particular database.
from
Killer
-
You cannot assign a table an owner that does not exist in that database. It might be possible that the bmssa login you were seeing as the owner was an orphaned user in that database. Where under Security > Logins, the bmssa login has no permissions for that database, but the database itself has a user with the same name. Look in Enterprise Manager for your database, under users, and see if bmssa exists but does not have an associated "Login Name".
if so, look up sp_change_users_login in BOL. it will associate the orphaned user to its proper SQL login.
Regards, Jim C
-
Hi ,
I looked it as an orphand user but we can use our application from bmssa login and even syncronise table and doing reindexing sometime.
but now we change over to sa.
But still i want to know that if we have orphaned user then why sql server allows to assign it as the owner of a table.When it have no permissions .
from
Killer
-
Maybe bmssa (I read this as BMS sa, an alternate sa account) is a server admin. In EM, double click the login id to get the properties window, then check the Server Roles tab. No explicit database-level permissions are necessary for a system admin.
-
Hi ,
No server role is assigned .
No database access permission assigned.
How can be bmssa behave as alternate sysadmin account with no server roles and no database access permissions.
from
Killer
-
Hi!!!!!
When restoring a database backup to another server, you may experience a
problem with orphaned users.........
Even though u deleted orphaned user still shows up (as an orphaned row) in the sysusers table:
USE <DataBase_Name>SELECT *FROM sysusersWHERE name = 'bmssa'Now......................To resolve orphaned users Add a temporary login using sp_addlogin. Specify the security identifier (SID) (from sysusers) for the orphaned user.
sp_addlogin @loginame = 'john', @sid = 0x32C864A70427D211B4DD00104B9E8A00- Drop the temporary alias that belongs to the aliased SID using sp_dropalias.
sp_dropalias 'john' - Drop the original user (now orphaned) using sp_dropuser.
sp_dropuser 'bmssa' - Drop the original login using sp_droplogin.
sp_droplogin 'john'
Enjoy......
Regards
Regards,
Papillon
- Drop the temporary alias that belongs to the aliased SID using sp_dropalias.
-
Dear Shashank,
Thanx for ur reply.If u have gone through the discussion then u might have see that it is not a orphand login because we execute some dml commands from Navision on sql server using that login.
I fixed the problem but i wanted to know the machenism that if a user not have
Server role assigned .
No database access permission assigned.
then how can we make the that user the owner of a object in a particular Database.
Hope it is more clear.
from
Killer
-
Raj, did your db changed owner?
-
Hi,
No, db owner not changed.When we install Microsoft Navision Axapta it creates a login BMSSA in sql server.
Hope this is more clear.
from
Killer
-
In response to your question,
"... if we have orphaned user then why sql server allows to assign it as the owner of a table ..."
You don't mention how the programmer changed the owner. Given that they have a minimum of db_owner privelages they could have run the sp_changeobjectowner procedure to re-assign the object ownership.
Looking at this procedure, all it does is check if the user specified exists in the sysusers table, which of course your orphan user BMSSA does, so the re-assignment completes successfully.
--------------------
Colt 45 - the original point and click interface
-
Sorry Phill
FOr late reply,
When we configure navision we assign a login name , so we assigned bmssa as recommended by microsoft.
So every user connect sql server by bmssa.All tables, views, reindexing are created and done by bmssa login only.
But i checked in sql server all table (1245 tables) owner is dbo why not bmssa.
as views are assigned to bmssa user only.
When i checked in sysuser i can see bmssa user but when i see the properties it do not have any permission.
But is going on at the back i dont know.
from
Killer
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply