Configuring SQL Server Express 2005 - The Not-So-Easy Part – Configuration
(Page 3 of 5 )
No doubt you strive for maximum security (the maximum possible within Windows that is) on your home PC. One of the most basic rules to follow in that case is not do your regular computing tasks as Administrator. Have an administrator account for installing applications, but then run as a regular 'user' for all other tasks, including programming.
The unfortunate result of taking that precaution, is that as a user you won't be able to login to SQL Express, only the administrator will. So we need to change that. But how do we do such a thing without a GUI admin tool? Simple, we go back to the old command prompt!
To do this I need to clarify two things.
- The SQL server name until this point has remained a mystery. Microsoft has decided that we have no need of changing the name, so it is preset as [MachineName]\SQLExpress. You can use 'localhost', '(local)', or even a period in place of your machine name, but I recommend specifying your machine name at lease in you application configuration files, so that you can easily spot where and what to change if you move SQL to another box, unlikely though that is.
- Though there is no GUI tools, Microsoft has not left us entirely up the creek. They provided a command line tool, called 'SQLCMD'.
So, fire up your command prompt (as administrator) and type in:
SQLCMD.exe -S (local)\SQLExpress -E
So now we are interacting with SQL Server Express, which as mentioned runs as 'SQLExpress' on the local machine.
To actually add a user, we're going to use the SQL Server System Security Stored Procedures. For now, these are the only options we have for administering security. The one we want right now is 'sp_grantlogin'. This will allow us or others to login to SQL server from non-administrator accounts. Type in:
> EXEC sp_grantlogin '[machineName]\[userName]'
> GO
Obviously you're substituting your machine and user name in there. Just thought I'd clarify that. Now we can add the user to SQL with another stored procedure: 'sp_adduser'. Type in:
> EXEC sp_adduser '[machineName]\[userName]', '[userNameInDB]'
> GO
It's good to specify the second parameter, the 'userNameInDB', so that you don't have to type in their full machine and user name every time. For instance, you could use 'Joe', and reference that user by 'Joe' in the other stored procedures that will come up.
You now want to make yourself a sysadmin, so that you can login to SQL Server from your user account and still have full abilities within the system. For that we will use the stored procedure 'sp_addsrvrolemember'. Type in:
> EXEC sp_ addsrvrolemember '[userName]', 'sysadmin'
> GO
And there you have it, you will now be able to log in and administer databases from the 'Database Explorer' within the express tools. Use these tools to create a new database. Remember the name of the database, as you'll need it for the next part! You can do this very easily by right-clicking on Data Connections, then click 'Create new SQL Server database...', and follow the wizard.
Next: Next – Connecting to Your Application >>
More MS SQL Server Articles
More By Justin Cook
/ 45 
