Little database user account script need help...

Hi there, I found this script on Microsoft's site for creating a Database, User, and Account in SQL 2005, it works really well and I like it alot, does anyone know how I can modify it to allow that user to view/edit their database via the Microsoft SQL Server Management Studio?

I tried adding this:

set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'exec sp_addrolemember ''db_owner'', ''' +
REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )

I have

declare @login_name sysname
declare @db_name sysname
declare @password sysname

SET @login_name = 'testuser'
SET @password ='testuserpw'
SET @db_name = 'testuserdb'


declare @sql nvarchar(max)

exec sp_validname @login_name
exec sp_validname @db_name

-- Creating the login
--
set @sql = 'CREATE LOGIN ' + QUOTENAME(@login_name) +
' WITH PASSWORD = ''' + REPLACE(@password, '''', '''''') + ''''
execute( @sql )
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name)
+ ' WITH CHECK_POLICY = ON'
execute( @sql )
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name) +
' WITH CHECK_EXPIRATION = OFF'
execute( @sql )

-- Allows the user to connect to the server
set @sql = 'GRANT CONNECT SQL TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Removes the ability to select from sys.databases
set @sql = 'DENY VIEW ANY DATABASE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Creating the database
--
set @sql = 'CREATE DATABASE ' + QUOTENAME(@db_name)
execute( @sql )

set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) +
' MODIFY FILE (NAME=''' + @db_name +
''', MAXSIZE=200, SIZE=5, FILEGROWTH=5)' -- Set max data file size
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) +
' MODIFY FILE (NAME=''' + @db_name +
'_log'', MAXSIZE=75, SIZE=2, FILEGROWTH=5)' -- set max log file size
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) +
' SET TRUSTWORTHY OFF'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) +
' SET AUTO_CLOSE ON'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) +
' SET AUTO_SHRINK ON'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) +
' SET AUTO_UPDATE_STATISTICS ON'
execute( @sql )
set @sql = 'ALTER DATABASE ' + QUOTENAME(@db_name) +
' SET AUTO_UPDATE_STATISTICS_ASYNC ON'
execute( @sql )

set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'CREATE USER ' + QUOTENAME(@login_name) +
' FOR LOGIN ' + QUOTENAME(@login_name)
execute( @sql )

-- Create a default full-text catalog
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'CREATE FULLTEXT CATALOG [' + @db_name + '_fulltext] AS DEFAULT'
execute( @sql )

-- Set the user to use the database created
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name) +
' WITH DEFAULT_DATABASE = ' + QUOTENAME(@db_name)
execute( @sql )

-- Add the user and configure
--
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'exec sp_addrolemember ''db_ddladmin'', ''' +
REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'exec sp_addrolemember ''db_datareader'', ''' +
REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'exec sp_addrolemember ''db_datawriter'', ''' +
REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )

-- ******************************
-- Additional privledges granted
-- ******************************

-- Gives user ability control the DBO schema
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'GRANT CONTROL ON SCHEMA::dbo TO ' + QUOTENAME(@login_name)
+ ' WITH GRANT OPTION'
execute( @sql )

-- Needed to execute showplan
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'GRANT SHOWPLAN TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to manipulate application roles
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'GRANT ALTER ANY APPLICATION ROLE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to manipulate roles
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'GRANT ALTER ANY ROLE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to view definitions of objects
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'GRANT VIEW DEFINITION TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed to create schemas
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'GRANT CREATE SCHEMA TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Needed for database-level DMVs
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'GRANT VIEW DATABASE STATE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- ******************************
-- Additional privledges denied
-- ******************************

-- Can be used to access the file system using SQL Server
-- credentials. (Both CREATE and ALTER.)
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY ALTER ANY ASSEMBLY TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Can be used to access the file system using the SQL Server
-- credentials. (Both CREATE and ALTER)
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY ALTER ANY ASYMMETRIC KEY TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Can be used to access the file system using the SQL Server
-- credentials. (Both CREATE and ALTER)
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY ALTER ANY CERTIFICATE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Partition, filegroups
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY ALTER ANY DATASPACE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Database DDL triggers
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY ALTER ANY DATABASE DDL TRIGGER TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Creates files for catalog
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY CREATE FULLTEXT CATALOG TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Can be used to rebuild catalogs and move their locations
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY ALTER ANY FULLTEXT CATALOG TO ' + QUOTENAME(@login_name)
execute( @sql )

-- No need to checkpoint
set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'DENY CHECKPOINT TO ' + QUOTENAME(@login_name)
execute( @sql )
GO

.

Relevant Pages

  • Re: Business objects, subset of collection
    ... SQL only works when the statements are ... all items (invoices) are subscribing for events. ... all items and all items has to execute the criteria evaluation, ... The features of a OO database is basically the same as of a network ...
    (comp.object)
  • Re: Multiple Database Security - How to handle
    ... There is no 'execute as' in SQL Server but you can simplify security ... Assuming the DM database contains tables that are accessed only by ...
    (microsoft.public.sqlserver.security)
  • Re: PHP/Oracle - Pulling data into array
    ... MySQL has the flaw that you have to stuff values into SQL statements, ... DATA with SQL. ... Depending on what interface and database you're using. ... time it comes across a new statement, it works out the best way to execute it. ...
    (comp.lang.php)
  • Re: PHP/Oracle - Pulling data into array
    ... MySQL has the flaw that you have to stuff values into SQL statements, ... DATA with SQL. ... Depending on what interface and database you're using. ... time it comes across a new statement, it works out the best way to execute it. ...
    (comp.lang.php)
  • Re: New to C# - DB question
    ... Firstly, you are interested in the System.Data namespace, also known as ... In there you will find a few sub namespaces for specific database ... is your sql statement. ... The former allows you to just execute some SQL, ...
    (microsoft.public.dotnet.languages.csharp)