Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can I assign SQL Server dbo permissions to a user, so it applies only for specified databases on a server? Currently using SQL Server 2005.

share|improve this question
add comment

migrated from stackoverflow.com Oct 30 '12 at 22:51

This question came from our site for professional and enthusiast programmers.

3 Answers

dbo stands for database owner. The only way to give that permission to a login is by creating a user that is associated with that login in a database and then give that user db_owner access to that database.

So granting this access is always happening a single database at a time. (But you could go through those steps for more then one database on a single login if desired.)

In SSMS you can find that setting on the "User Mapping" page of the login properties dialog for the login in question.

share|improve this answer
add comment

The code for SQL would be:

USE [DBName] --Insert DBName Here
GO

CREATE USER [SQLServerorWindowsUser]
  FOR LOGIN [SQLServerorWindowsUser] --add the user to the DB
GO

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO

If you already have the user in the database you only need to execute:

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO
share|improve this answer
add comment

dbo is a user and db_owner is database owner role. Only member of the sysadmin fixed server role are implicitly mapped to dbo user. However, any user in a database can be added to db_owner role and doing so give them all permission for that database.

an I assign SQL Server dbo permissions to a user, so it applies only for specified databases >>on a server? Currently using SQL Server 2005.

I think you want to add user and give that user db_owner permission.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.