Permissions for Domain user account

Hi,
I want to use a domain user account not belonging to local admin or domain admin groups in SQL 2000/2005 Enterprise edition. This is what I've done so far..
On the machine that is the Domain Controller:
- installed SQL 2005 as a domain admin

- created a domain user account using Active Directory Users and Computers. This user is only

"Member of" domain users; not any Administrators group.

- added this user to SQL Server Management Studio->Logins and in Server Roles assigned

sysadmin role.
Question 1: Do I need to give any additional permissions to this user to work with SQL?
Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?
Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.

Thanks!

Hi there,

Just a few of my thoughts....

Question 1: Do I need to give any additional permissions to this user to work with SQL?

Well, going off what you have said (making the domain user part of the sysadmin server role) this will allow the domain user to do any action on your database server.

Whether this is a good thing or not is debatable as it's pretty good practice to only give a user the lowest level of permissions they need in order to do whatever work they need to do. Not knowing your exact situation, giving the domain user the level of privilege you have might be overkill - but like I said it depends on what you're intending etc.

I would recommend you review security topics in SQL Server Books Online and use the info there in conjunction with your knowledge of the circumstances to select the appropriate strategy for giving permission to this user and any other users whose accounts you need to add.

Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?

There are a few options here, I'll go into a few....

A) Yes you can use the OSQL or iSQL command line utilities to execute T-SQL if you're working with SQL Server 2000. Once again, SQL Server Books Online is a good resource for this.

If you're working with SQL Server 2005, it's better to use sqlcmd if you're looking to do things via a command line (http://msdn2.microsoft.com/en-us/library/ms170207(SQL.90).aspx)

B) It's easier to use graphical tools, I think. If you can use something like Query Analyzer (SQL Server 2000) or Management Studio (SQL Server 2005) to connect to your DB as the domain user (e.g. logging onto a machine with those tools installed as the domain user and then using those tools to connect to your database server) then that would probably be easier

Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.

From what you've written it seems like you're doing the right thing. Here's a tutorial on the complete process which may help: http://www.windowsnetworking.com/articles_tutorials/Windows_2003_Terminal_Services_Part1.html
(Note that it's split into two parts...The link I've included is to the first part but the first part connects to the second part via another link)

TechNet isn't bad either: http://technet2.microsoft.com/windowsserver/en/technologies/featured/termserv/default.mspx

Like I said, from what you've written it's a little hard to gauge the exact problem but it seems like you're doing the right things. However, if you want to run through the steps in the articles & stuff I've placed links to above then things should work for your Terminal Services setup (they did for me)

========

Hope this helps a bit
|||

Thanks alot for your help. Sorry for a delayed reply as I was reading the material you suggested and the good news is that I am able to implement the same and have got things working.
ref - Question 1: As for the permissions, as you suggested, I no longer give the user sysadmin role. The user has dbowner role and it suffices.
ref-Question 2: I used Query Analyser itself by logging in to the machine as the domain user.
ref- Question 3: I could not get Remote Desktop to work for the user. I guess some licensing issue. As a workaround, I used VNC to login as the domain user.

Thanks again.