giammin

Database and user/login creation with default values

Nov 13th, 2013
319
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.82 KB | None | 0 0
  1. SET ANSI_NULLS ON
  2. SET QUOTED_IDENTIFIER ON
  3. SET ANSI_PADDING ON
  4.  
  5. declare
  6.      @dbname nvarchar(50) ,
  7.      @userName nvarchar(50) ,
  8.      @password nvarchar(50);
  9. SET @dbname = N'DATABASENAME';
  10. SET @userName = N'DBUSERNAME';
  11. SET @password= N'DBUSERPASSWORD';
  12.  
  13. IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
  14. BEGIN
  15.     exec ('
  16.     DROP USER '+  @userName + '
  17.     DROP login '+ @userName + '
  18.     alter database '+  @dbname + '
  19.     SET OFFLINE
  20.     WITH ROLLBACK IMMEDIATE;
  21.     ALTER DATABASE '+  @dbname + '
  22.     SET ONLINE;
  23.     DROP DATABASE '+  @dbname)
  24. END
  25.  
  26. EXEC ('CREATE DATABASE '+ @dbname);
  27. EXEC (@dbname+ '.[dbo].[sp_fulltext_database] @action = ''disable''');
  28. EXEC ('ALTER DATABASE '+ @dbname + ' SET COMPATIBILITY_LEVEL = 100');
  29. EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_NULL_DEFAULT OFF');
  30. EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_NULLS ON');
  31. EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_PADDING ON');
  32. EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_CLOSE OFF');
  33. EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_SHRINK OFF');
  34. EXEC ('ALTER DATABASE '+ @dbname + ' SET QUOTED_IDENTIFIER ON');
  35. EXEC ('ALTER DATABASE '+ @dbname + ' SET RECOVERY FULL');
  36. EXEC ('ALTER DATABASE '+ @dbname + ' SET PAGE_VERIFY CHECKSUM');
  37. EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_WARNINGS ON');
  38. EXEC ('ALTER DATABASE '+ @dbname + ' SET ARITHABORT ON');
  39. EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_CREATE_STATISTICS ON');
  40. EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_UPDATE_STATISTICS ON');
  41. EXEC ('ALTER DATABASE '+ @dbname + ' SET CURSOR_CLOSE_ON_COMMIT OFF');
  42. EXEC ('ALTER DATABASE '+ @dbname + ' SET CURSOR_DEFAULT  GLOBAL');
  43. EXEC ('ALTER DATABASE '+ @dbname + ' SET CONCAT_NULL_YIELDS_NULL OFF');
  44. EXEC ('ALTER DATABASE '+ @dbname + ' SET NUMERIC_ROUNDABORT OFF');
  45. EXEC ('ALTER DATABASE '+ @dbname + ' SET RECURSIVE_TRIGGERS OFF');
  46. EXEC ('ALTER DATABASE '+ @dbname + ' SET  ENABLE_BROKER');
  47. EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF');
  48. EXEC ('ALTER DATABASE '+ @dbname + ' SET DATE_CORRELATION_OPTIMIZATION OFF');
  49. EXEC ('ALTER DATABASE '+ @dbname + ' SET TRUSTWORTHY OFF');
  50. EXEC ('ALTER DATABASE '+ @dbname + ' SET ALLOW_SNAPSHOT_ISOLATION OFF');
  51. EXEC ('ALTER DATABASE '+ @dbname + ' SET PARAMETERIZATION SIMPLE');
  52. EXEC ('ALTER DATABASE '+ @dbname + ' SET READ_COMMITTED_SNAPSHOT OFF');
  53. EXEC ('ALTER DATABASE '+ @dbname + ' SET HONOR_BROKER_PRIORITY OFF');
  54. EXEC ('ALTER DATABASE '+ @dbname + ' SET  READ_WRITE');
  55. EXEC ('ALTER DATABASE '+ @dbname + ' SET  MULTI_USER');
  56. EXEC ('ALTER DATABASE '+ @dbname + ' SET DB_CHAINING OFF');
  57.  
  58. EXEC ('create login '+  @userName + ' with password = '''+ @password+ ''', default_database = ' + @dbname);
  59. EXEC ('use  '+ @dbname+ ' create user '+ @userName + ' for login '+ @userName);
  60. EXEC ('use  '+ @dbname+ ' EXEC sp_addrolemember  ''db_owner'', '+  @userName);
Advertisement
Add Comment
Please, Sign In to add comment