Sql-server – MS SQL Server create user who can access only those databases which they have created

Use a database role. Database roles are database specific (obviously) so you can't create a role that grant's permissions to multiple databases at once. However within the database you create a role either through the GUI or using the command CREATE ROLE <rolename>. Once it is created you can then grant the role permissions just like you would a user. Then you add users to the role. Again you can either use the GUI or the command EXEC sp_addrolemember '<rolename>','<username>'. And last but not least there are some pre-defined roles at both the server and database level. (You can't create server roles in SQL 2008.) You can not make changes to the pre-defined roles but among others there is db_datareader which grants SELECT permission to every table in the database. Oh, and you can add your user defined role to the pre-defined role. So you could create a role MyReadOnlyRole and add it to db_datareader and then grant it EXECUTE permissions to various stored procedures, functions etc. Or EXECUTE permission to the database itself for that matter. (That gives permission to execute any SP, function etc in the database.)

May not be pretty, but you could use a trigger on database creation that executes as a privileged user of your choice:

CREATE TRIGGER [database_setup]

    ON ALL SERVER
    WITH EXECUTE AS 'privuser'
    FOR CREATE_DATABASE
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @db nvarchar(100)
    DECLARE @sql nvarchar(MAX)
    DECLARE @data XML
    SET @data = EVENTDATA()

    SELECT @db = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)');
    SELECT @sql = ('ALTER AUTHORIZATION ON DATABASE::' + @db + ' TO sa;');
    sp_executesql @sql;
    END
GO

ENABLE TRIGGER [database_setup] ON ALL SERVER
GO