blob: b21473256911faaa09d83200d1d3905079c3b9f6 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Tango.Core.DB;
namespace Tango.Web.SMO
{
public class SmoManager : IDisposable
{
private ServerConnection _connection;
private Server _server;
private static Random random = new Random();
public SmoManager()
{
_connection = new ServerConnection(WebConfig.DB_ADDRESS, WebConfig.DB_USER_NAME, WebConfig.DB_PASSWORD);
_server = new Server(_connection);
}
public DbCredentials CreateRandomLoginAndUser()
{
var database = _server.Databases.OfType<Database>().SingleOrDefault(x => x.Name == WebConfig.DB_CATALOG);
String userName = GetRandomString(36);
String password = System.Web.Security.Membership.GeneratePassword(16, 2);
Login login = new Login(_server, userName);
login.LoginType = LoginType.SqlLogin;
login.DefaultDatabase = WebConfig.DB_CATALOG;
login.PasswordPolicyEnforced = false;
login.Create(password);
User user = new User(database, userName);
user.Login = userName;
user.Create();
user.AddToRole("db_datareader");
// Have to add this permission so user can view column definitions (default values etc..) !!!
DatabasePermissionSet dbPermSet = new DatabasePermissionSet();
dbPermSet.Add(DatabasePermission.ViewDefinition);
database.Grant(dbPermSet, user.Name);
return new DbCredentials() { UserName = userName, Password = password };
}
public void DeleteLoginAndUser(String userName)
{
var database = _server.Databases.OfType<Database>().SingleOrDefault(x => x.Name == WebConfig.DB_CATALOG);
var user = database.Users.OfType<User>().SingleOrDefault(x => x.Name == userName);
if (user != null)
{
user.Drop();
}
Login login = _server.Logins.OfType<Login>().SingleOrDefault(x => x.Name == userName);
if (login != null)
{
login.Drop();
}
}
public string GetRandomString(int length)
{
const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
return "TEMP_" + new string(Enumerable.Repeat(chars, length).Select(s => s[random.Next(s.Length)]).ToArray());
}
public void Dispose()
{
_connection.Disconnect();
}
}
}
|
