sp_addalias and stuff
-
Help
Is it possible to alias a user as dbo and then access a dbo owned table with the alias name..
For example
sp_addalias 'testuser', 'dbo'
--
select * from testuser.sysobjects
--
I am seeing something like this in a vendor provided application and I can't seem to duplicate it?
Any thoughts???
Thanks
"Keep Your Stick On the Ice" ..Red Green -
You can use the sp_addalias 'login_name', 'name_in_db' syntax to add a user and alias that user to dbo. That user must not currently exist in the database if you use this syntax. This also gives the user dbo permissions. Not much different than assigning that person to the db_owner database role. Be careful using this syntax. This may not give you the desired results (e.g. unable to just drop the database user now because he is dbo and dbo owns objects) This stored procedure is there for backward compatibility. The preferred method is assigning users to roles.
Lori
-
What about selecting from a table using the alias name?
"Keep Your Stick On the Ice" ..Red Green -
I created a database and added tables. I created a login that had no permissions on the database, then logged in. The user was unable to access the db. I then used the sp_addalias to alias the user to dbo in that particular database, logged out, logged back in, and the user had db_owner permissions (able to create tables, read, etc). If all you need is the ability to allow your users to read from the tables in the database, a better solution would be to add them to the db_datareader role. Remember, that by using this alias idea, your user gets dbo permissions (full permissions to drop/create objects, select, insert, update, delete, etc).
Lori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply