Solved

An admin user with SQL authentication'

Posted on 2007-04-11
12
161 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:amoran
  • 4
  • 3
  • 3
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18888966
go to the sql login, and add the server role server admin to that login
0
 

Author Comment

by:amoran
ID: 18889121
"go to the sql login"

Where is that?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18889180
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.
0
 

Author Comment

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

Thanks!
0
 

Author Comment

by:amoran
ID: 18889492
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
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 42

Expert Comment

by:dqmq
ID: 18891136
>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.  
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 25 total points
ID: 18891280
>>So this means that username can login to all databases I create?
if you grant server admin role, yes, it can login to all databases your have or will create on that server WITHOUT giving explicit database permissions!

if that is too much, to not assign that server admin role, but map the login to a user in the respective database(s), and grant the roles you want to give, for example the database owner  (db_owner) role.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18892650
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).  
0
 

Author Comment

by:amoran
ID: 18896130
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?


0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 25 total points
ID: 18904210
Like I said, check out the user mapping dialog under Server Security and then also check out Security under each database.  

Or try this:
Logon using SQL Server Management Studio using your SQL Server login. Then open a new query window and run:
 
"USE yourdatabase"
"SELECT USER".  Likely, whatever USER is reported back does not have the necessary permissions.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now