Post reply

Using an table user alias is not returning rows

  • April 7, 2005 at 2:58 am

    #163219

    Hello

    I'm new to SQL server (2000 SP3a) and installing a third party schema.  All objects belong to dbo but the application uses an account called dummy to access information.

    I created dummy as a login account (not a user) then ran:

    sp_addalias 'dummy', 'dbo'

    If I run 'select * from dummy.temp' I get Msg 208.

    If I run 'select * from dbo.temp' it works.

    How can I get the dummy qualifier to work????

    Thanks in advance

  • April 8, 2005 at 5:15 am

    #551422

    By using sp_addalias you map the login to a specific user, but you don't transfer ownership of objects to this user, which is what you want to achieve.

    You can change ownership of objects by using next proc:

    sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

    Pay attention: if you don't want users connecting to your sql server with this dummy login to have full db owner rights wich include:

    - create, alter, drop tables from any user in the database

    - create, alter, drop and execute procedures from any user in the database

    - select, insert, update, delete any data

    - lots of other fun stuff that could make you lose your hair...

    then you shouldn't map the login to dbo. A better approach would be the following:

    Create a dummy user in the database (if it doesn't exist already), map the dummy login to this user and assign appropriate rights to this user (either directly to the user or to a userdefined role to which the user can be assigned).

    Assigning appropriate rights could be done like this:

    You could leave the ownership of the tables to dbo, create stored procedures that insert, update, delete and access the data. Create these procedures with sysadmin or dbo priviliges so they will be owned by dbo. This will result in a chain of ownership and granting execute on these procedures to your dummy user or to the userdefined role to wich he belongs would allow him to execute the procedures without granting excessive rights (he won't be able to query tables directly)...

  • April 8, 2005 at 5:20 am

    #551424

    Many thanks for clearing the situation. 

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply