Limit admin access to a single database SQL2005

10 pts.
Tags:
SQL 2005
SQL Database
SQL Permissions
I want to set up an account similar to sa but limit the rights to a particular db.
ASKED: November 18, 2008  7:34 PM
UPDATED: November 19, 2008  2:47 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Once you have created your database, you need to set up a new login. To do so:

1.

Open the SQL Server Enterprise Manager
2.

Expand the server folder for the server on which you have created your database in the navigation pane
3.

Expand the Security sub-folder
4.

Right-click Logins and select New login…
5.

Enter a name for the new login
6.

Select SQL Server Authentication and enter a password
7.

Select the database you want from the Default database drop-down list
8.

Open the Database Access tab and tick the database you have created
9.

Tick the db_owner box to assign permissions

10.

Click OK
11.

Re-enter the new password and click OK to complete the login creation process

Once you have created your database, you need to set up a new login. To do so:

1.

Open the SQL Server Management Studio
2.

Expand the server folder for the server on which you have created your database in the navigation pane
3.

Expand the Security sub-folder
4.

Right-click Logins and select New login…
5.

Enter a name for the new login
6.

Select SQL Server Authentication and enter a password
7.

In the Default database list, select the database
8.

Open the User Mapping page and tick the database you created
9.

Tick the db_owner box to assign permissions

10.

Click OK

Enabling mixed mode server authentication

The process of enabling mixed mode authentication varies depending on which version of SQL Server you are using. Select the correct version below for further details.

*

SQL Server 2005
*

SQL Server 2000

Enabling mixed mode server authentication for SQL Server 2005

To enable mixed mode authentication complete the steps below:

1.

Launch the SQL Server Management Studio
2.

Login to the database server with the System Administrator user (this was defined when the database server was installed)
3.

Right-click on the on the database connection in the Object Explorer window and select Properties

4.

Select the Security page
5.

From the Server authentication options click SQL Server and Windows Authentication mode
6.

Click OK to apply the changes

You can now use the database user you created to access the database.
Enabling mixed mode server authentication for SQL Server 2000

To enable mixed mode authentication complete the steps below:

1.

Launch the SQL Server Enterprise
2.

Login to the database server with the System Administrator user (this was defined when the database server was installed)
3.

Right-click on the on the database connection in the Console Root window and select Properties

4.

Select the Security tab
5.

From the Security options click SQL Server and Windows
6.

Click OK to apply the changes

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following