You assign permissions using GRANT statement.

>I am creating SQL 2005 databases for students for a class I'm teaching. I write this script which takes a student's Id and creates a Login, Database and DB user. So far this all works fine, except for rights. How do I assign rights for users?
>
>

>
>USE [master]
>GO
>
>DECLARE @UserName VARCHAR(5)
>DECLARE @SqlStatement nvarchar(MAX);
>
>SET @UserName = '75080'
>
>SET @SqlStatement = 'CREATE LOGIN [NC-Coleman\' + @UserName + '] FROM WINDOWS'
>EXECUTE (@SqlStatement);
>
>EXECUTE ('CREATE DATABASE [' + @UserName + ']')
>
>SET @SqlStatement = N'USE [' + @UserName + '];CREATE USER [' + @UserName + '] FOR LOGIN [NC-Coleman\' + @UserName + ']'
>EXECUTE (@SqlStatement);
>GO
>