May 14, 2003 at 8:41 am
How do I give alias to login as dbo. Let me know its kind of urgent.
I could n't understand sp_addalias do
venkatesh
venkatesh
May 14, 2003 at 8:43 am
Do you truly want them aliased to dbo or just have db_owner rights?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 14, 2003 at 9:48 am
If this is SQL 7 or 2k, you don't want to alias. That was a prior feature in v6.5. In 7 ro 2k you would probably want to give them the db_owner role.
I keep tight control on this, if you tell us why we can guide you on whether you really need this.
Steve Jones
May 14, 2003 at 10:55 am
The reason I am asking is , there is a replication setup for one of our client database and sometime the client side DBA will create objects in our databases, but the login has is which has db_owner standard role assinged to that login but when he creates the objects it is creating has login.object_name, but the application is strictly looking for dbo objects only. Please help in this regards.
venkatesh
venkatesh
May 14, 2003 at 11:28 am
May 14, 2003 at 11:36 am
I understand that we can change the object owner after creation of object by the below procedure, but is their any way while creating object logging through non-sysadmin login. Please let me know.
venkatesh
venkatesh
May 14, 2003 at 11:41 am
If you don't want to change the owner, in the database | users, select the user name, right click, properties, then select the db_owner role.
Steve Jones
May 14, 2003 at 11:44 am
Is there any way where we can give alias to the login as dbo.
venkatesh
venkatesh
May 14, 2003 at 12:41 pm
Drop the user from the db. Then run:
sp_addalias 'dbo', 'whatevertheloginis'
If you put the user in db_owner when they create objects they will be prefixed with the login unless the script explicitly includes the dbo owner. This way any objects they create are owned by dbo unless they choose not to.
MS does consider it outdated, but it has it's uses.
Andy
May 14, 2003 at 12:46 pm
Keep in mind that for object creation, if a user is a member of the following roles, the user can create an object with dbo as owner:
db_owner
db_ddladmin
When creating the object, you'd specify the owner with a two part naming convention. For instances:
CREATE TABLE dbo.TestTable (
TestID int)
GO
As Andy has pointed out, if you want the user to be dbo, the sp_addalias is the only way. And a slight correction on the order:
sp_addalias @loginame, @name_in_db
so it would be:
sp_addalias 'TheUser', 'dbo'
Of course, if you only want the user to create objects as dbo, you can simply grant db_ddladmin and be done.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 14, 2003 at 1:08 pm
Oops! Thanks for catching that.
Andy
May 14, 2003 at 1:14 pm
Thanks Andy, I know about this before but i was not sure about it and moreover nobody has clear answers prior to last two answers.
venkatesh
venkatesh
October 26, 2005 at 2:37 pm
Once I've used sp_addalias, is there a way after the fact to see what has been aliased ?
I can run:
SELECT * FROM sysusers
where isaliased ='1'
To show me which logins have been aliased, but how do I know they were aliased to 'dbo' ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply