asked on

An admin user with SQL authentication'

Hi

I've been asked to setup a new database called DB1 and

'set up an admin user with SQL authentication'

Any ideas how I do this in SQL Server 2005?

If I expand DB1
I see
Security
Users

What should I do next?

thanks
AM

Guy Hengel [angelIII / a3]

go to the sql login, and add the server role server admin to that login

amoran

ASKER

"go to the sql login"

Where is that?

Guy Hengel [angelIII / a3]

you should have a folder "security" (not on the database level, but on the server level), you can edit/create (SQL) logins there. on such a login, you can assign fixed server roles, among them a server admin role.

amoran

ASKER

So I should create a new login?
With windows or SQL Server authentication?
And give them the server role 'server admin'?

Thanks!

amoran

ASKER

Ok so I created a new login with sql server authentication and gave them the server role 'server admin'.

So this means that username can login to all databases I create?

Thanks

dqmq

>So this means that username can login to all databases I create?

You've only granted permissions at the server level. You also need to adminster security at the database level. Check out the user mapping dialog under Server Security and then also check out Security under each database.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dqmq

AngelIII is right about server admins not requiring explicit grants for database permissions. However, the question was about setting up an admin user, not an admin login (perhaps I'm interpreting to literally).

amoran

ASKER

Thanks guys ...
I'm trying to access the database from a SQLDataSource in an ASP.NET website.
When I configure the datasource the database shows up when I select the 'Use Windows Authentication' but not if I choose 'Use SQL Server Authentication' and enter the username and password that I created in SQL Server 2005.

Any ideas?

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial