Security: Using the Command Line
Last updated May 26, 2005.

Above, we learned the concepts behind SQL Security. We learned about Server logins and database logons, about object and statement permissions.
Although the graphical tools provide the simplest method of working with SQL Security, it's often not practical. You need a way to repeat the same steps many times, and the command line offers just that. Using a tool such as a text editor or even a spreadsheet can rapidly add thousands of users and modify permissions on your databases.
As we learned previously, you need to create a server login account first. Next, you tie the user to a database and finally you grant or deny them permissions to various objects in a database. 
Recall that we discussed the two types of login authentication: Windows and SQL Server and Windows. The commands I'm about to show you depend on the type of authentication your server has installed.
To create SQL Server logins using SQL authentication is sp_addlogin. The format looks like this:
sp_addlogin 'Buck', 'password'
With the name Buck and a password of password (which is not a good password).
If you have Windows authentication set on your server, you use a different command, and you don't have to specify the password: 
sp_grantlogin 'HQ\Buck'
Notice that you have to specify the domain name. SQL Server will "trust" that account, so it doesn't track the password.
Those aren't the only commands you can use to work with server logins. Here's a few from Books Online:
sp_defaultdb	Sets a login's default database	
sp_denylogin	Denies a login	
sp_droplogin	Removes a login	
sp_helplogins	Displays logins	
sp_password	Changes a login's password	
sp_revokelogin	Revokes a login	
sp_validatelogins	Shows "orphans" in the database. These are logins that exist in SQL Server, but no longer exist as a Windows account.	
Most of these are self-explanatory, but you can find out more in the Books Online.
Once you have the logins created, it's time to tie the login to access a database using the sp_grantdbaccess command:
sp_grantdbaccess 'HQ\Buck', 'Buck'
Make sure you're "in" the database to which you're granting the account access. You can do that by pulling down the database name menu in Query Analyzer or (safer) by type the USE databasename command. What you're doing with this command is setting the domain account you used a moment ago (HQ\Buck) to a user account called "Buck" in this database. Yes, you could name it something different, but you can also drive your car into a lake. There aren't a lot of good reasons to do either one.
Just as with the logins, there are other commands to help you work with the logons. From Books Online: 
sp_helpuser	Displays information about a database user	
sp_revokedbaccess	Removes a login from the database	
I'm not going to spend any time on working with roles in this tutorial; we'll cover the entire concept of roles later on. 
Now that you have your server logins, and the database users are created, you need to set the permissions for the various database objects.
You can grant or deny permissions to users or roles. The main commands to use are GRANT, DENY and REVOKE. Remember that permissions are layered on each object, so permissions need to be set on views and the tables they references, unless they are owned by the same account. Remember that if that's true, you only have to set permissions on the "parent" object. 
Not only does each object require the security, but each statement requires the security. In other words, if you need to allow access to a few columns in a table, you have to issue the GRANT command on the table and the SELECT, INSERT, UPDATE, and DELETE verbs. The verbs are self-explanatory. Using this "layered" security approach, you can set certain accounts (or roles) to be able to see (SELECT) certain portions of data from a table and not be able to change (UPDATE) or erase (DELETE) it. 
The one verb I didn't mention is the EXECUTE permission. As long as one account created the objects in the database, you only need to set the permission on the stored procedure.
The GRANT verb allows access to an object. It looks like this:
GRANT SELECT
ON authors
TO [HQ\Buck]
You can use more than one verb here – just add a comma between them, like this:
GRANT SELECT, UPDATE
ON authors
TO [HQ\Buck]
You don't have to type every verb if you want to give every permission to an account. Just use the ALL permission, which is basically everything.
What if you want the opposite? If you don't want to the account to access an object, use the DENY verb:
DENY INSERT
ON authors
TO [HQ\Buck]
Remember the REVOKE command? I know we haven't talked about roles yet, but let's say "Buck" is a member of a role called "Normal" (no real danger of that). Let's say that we don't want Buck to be able to access a table, even though his group (Normal) still needs to. For that, use the DENY verb. But let's say now you want Buck not to have access to the table, but if he's the member of a group that does need access, we don't want to affect that. For that situation, use the REVOKE command. It looks like this:
REVOKE SELECT 
ON authors
FROM [HQ\Buck]
Well, those are the security commands. I've introduced the basic commands, but you can see that setting up the security structure gets complex. To do that properly:
•	Identify the objects in your database
•	Identify the users who need access
•	Create the database roles by function (more next week)
•	Set the permissions for the roles on the objects
•	Add the users to the roles