Создание пользователей баз данных SQL Server 2005, CREATE USER, свойства пользователей
Создать пользователя базы данных можно:
q на графическом экране из контейнера Имя_базы_данных | Security | Users в Management Studio ;
q при помощи команды CREATE USER (хранимая процедура sp _ adduser , которая использовалась для этой цели в предыдущих версиях SQL Server , оставлена только для обеспечения обратной совместимости). Например, команда на создание пользователя User1 , которому будет соответствовать логин SQL Server Login1 со схемой по умолчанию dbo , может выглядеть так:
CREATE USER User1 FOR LOGIN Login1 WITH DEFAULT_SCHEMA = dbo;
При создании пользователя вам нужно будет указать:
q имя пользователя (User name ), к которому применяются те же правила, что и для других объектов SQL Server ;
q логин (SQL Server или Windows ), которой будет назначен пользователю этой базы данных. После создания пользователя назначенный ему логин изменять будет нельзя. Можно создать пользователя, которому не будет назначен никакой логин (при помощи переключателя Without login ). Такому пользователю уже не получится назначить логин. Пользователи этого типа - без логинов - используются только для дополнительной настройки безопасности в Service Broker . Отметим также, что если какой-то логин уже был назначен пользователю, то другому пользователю одновременно назначить его нельзя;
q сертификат (Certificate name ) или асимметричный ключ (Key name );
q схему по умолчанию (Default schema );
q для каких схем этот пользователь будет являться владельцем (Owned schemas );
q какие роли базы данных (Database roles ) будут ему назначены.
Обязательных параметра всего два - имя пользователя и логин.
На вкладке Securables пользователю можно сразу же предоставить разрешения на объекты базы данных. Речь о предоставлении разрешений пойдет в следующих разделах. Вкладка Extended Properties позволяет определить дополнительные пользовательские свойства для данного объекта. Применяются они для тех же целей, что и расширенные свойства баз данных (см. разд. 4.8) .
Изменение свойств пользователя и его удаление производится из того же контейнера в Management Studio , что и создание пользователя, а также при помощи команд ALTER USER/DROP USER . Удалить пользователя, владеющего какими-либо объектами в базе данных, нельзя.
Добавление пользователей базы данных
Исходники баз данныхПользователь может войти в систему баз данных, используя учетную запись пользователя Windows или регистрационное имя входа в SQL Server. Для последующего доступа и работы с определенной базой данных пользователь также должен иметь учетную запись пользователя базы данных. Для работы с каждой отдельной базой данных требуется иметь учетную запись пользователя именно для этой базы данных. Учетную запись пользователя базы данных можно сопоставить с существующей учетной записью пользователя Windows, группой Windows (в которой пользователь имеет членство), регистрационным именем или ролью.
Управлять пользователями баз данных можно с помощью среды Management Studio или инструкций языка Transact-SQL. Оба эти способа рассматриваются в следующих подразделах.
Управление пользователями базы данных с помощью среды Management Studio
Чтобы добавить пользователя базы данных с помощью среды Management Studio, разверните узел сервера в окне Object Explorer и в нем папку "Databases", в этой папке разверните узел требуемой базы данных, а в ней папку "Security". Щелкните правой кнопкой мыши папку "Users" и в контекстном меню выберите пункт New User. Откроется диалоговое окно Database User - New, в котором следует ввести имя пользователя User name и выбрать соответствующее регистрационное имя Login name:
Здесь можно также выбрать схему по умолчанию для данного пользователя.
Управление безопасностью базы данных посредством инструкций языка Transact-SQL
Для добавления пользователя в текущую базу данных используется инструкция CREATE USER . Синтаксис этой инструкции выглядит таким образом:
CREATE USER user_name Соглашения по синтаксису
Параметр user_name определяет имя, по которому пользователь идентифицируется в базе данных, а в параметре login указывается регистрационное имя, для которого создается данный пользователь. В параметрах cert_name и key_name указываются соответствующий сертификат и асимметричный ключ соответственно. Наконец, в параметре WITH DEFAULT_SCHEMA указывается первая схема, с которой сервер базы данных будет начинать поиск для разрешения имен объектов для данного пользователя базы данных.
Применение инструкции CREATE USER показано в примере ниже:
USE SampleDb; CREATE USER Vasya FOR LOGIN Vasya; CREATE USER Alex FOR LOGIN WITH DEFAULT_SCHEMA = poco;
Для успешного выполнения на вашем компьютере второй инструкции примера требуется сначала создать учетную запись Windows для пользователя Alexandr и вместо домена (сервера) ProfessorWeb указать имя вашего сервера.
В этом примере первая инструкция CREATE USER создает пользователя базы данных Vasya для пользователя Vasya учетной записи Windows. Схемой по умолчанию для пользователя Vasya будет dbo, поскольку для параметра DEFAULT_SCHEMA значение не указано. Вторая инструкция CREATE USER создает нового пользователя базы данных Alex. Схемой по умолчанию для этого пользователя будет схема poco. (Параметру DEFAULT_SCHEMA можно присвоить в качестве значения схему, которая в данное время не существует в базе данных.)
Каждая база данных имеет своих конкретных пользователей. Поэтому инструкцию CREATE USER необходимо выполнить для каждой базы данных, для которой должна существовать учетная запись пользователя. Кроме этого, для определенной базы данных регистрационное имя входа в SQL Server может иметь только одного пользователя базы данных.
С помощью инструкции ALTER USER можно изменить имя пользователя базы данных, изменить схему пользователя по умолчанию или переопределить пользователя с другим регистрационным именем. Подобно инструкции CREATE USER, пользователю можно присвоить схему по умолчанию прежде, чем она создана.
Для удаления пользователя из текущей базы данных применяется инструкция DROP USER . Пользователя, который является владельцем защищаемых объектов (объектов базы данных), удалить нельзя.
Схемы базы данных по умолчанию
Каждая база данных в системе имеет следующие схемы по умолчанию.
В этой небольшой статье мы разберём SQL-запрос , который позволяет добавлять новых пользователей к базе данных , а также изменять параметры уже существующих.
Давайте приведу сразу пример SQL-запроса на создание нового пользователя базы данных :
GRANT SELECT , INSERT , UPDATE , CREATE , PROCESS ON * . * TO "MyUser"@"localhost" IDENTIFIED BY "123456" WITH GRANT OPTION MAX_QUERIES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 10
Теперь давайте разберём SQL-запрос на изменение существующих пользователей :
GRANT SELECT , INSERT , UPDATE , CREATE , PROCESS ON * . * TO "MyUser"@"test1.ru" IDENTIFIED BY PASSWORD "565491d704013245" WITH GRANT OPTION MAX_QUERIES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 10
Как видите, синтаксис АБСОЛЮТНО такой же, как и на создание нового пользователя. Единственное отличие - это то, что пользователь "MyUser " уже существует. Вот и всё.
И, наконец, удаление пользователя из базы данных . Тут есть один интересный момент. Дело в том, что данные пользователей базы данных сами хранятся в таблицах базы данных. Поэтому, чтобы удалить пользователя надо удалить записи в соответствующих таблицах. Вот код:
DELETE FROM `user` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)
DELETE FROM `db` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)
DELETE FROM `tables_priv` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)
DELETE FROM `columns_priv` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)
Много, не спорю, но это единственный способ удалить пользователя. Либо вручную через PHPMyAdmin . Единственное, что надо изменить в этом шаблоне - это имя пользователя и хост, а всё остальное Вам вряд ли придётся менять.
5 ответов
Основываясь на вашем вопросе, я думаю, что вы можете немного смутить разницу между пользователем и входом. Логин - это учетная запись на SQL Server в целом - кто-то, кто может войти на сервер и у кого есть пароль. Пользователь - это вход с доступом к определенной базе данных.
Создание входа легко и необходимо (очевидно) сделать до создания учетной записи пользователя для входа в конкретную базу данных:
CREATE LOGIN NewAdminName WITH PASSWORD = "ABCD" GO
Вот как вы создаете пользователя с привилегиями db_owner, используя только что объявленный Логин:
Use YourDatabase; GO IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N"NewAdminName") BEGIN CREATE USER FOR LOGIN EXEC sp_addrolemember N"db_owner", N"NewAdminName" END; GO
Теперь логины немного более жидкие, чем я, кажется, выше. Например, учетная запись пользователя автоматически создается (в большинстве установок SQL Server) для учетной записи администратора Windows при установке базы данных. В большинстве ситуаций я просто использую это, когда я управляю базой данных (у нее есть все привилегии).
Однако, если вы собираетесь получать доступ к SQL Server из приложения, вам нужно настроить сервер для "Смешанного режима" (как для Windows, так и для SQL-входа) и создать Login, как показано выше. Затем вы получите "GRANT" priviliges для этого SQL-входа на основе того, что необходимо для вашего приложения. См. для получения дополнительной информации.
ОБНОВЛЕНИЕ: Аарон указывает на использование sp_addsrvrolemember для назначения подготовленной роли вашей учетной записи. Это хорошая идея - быстрее и проще, чем предоставление привилегий вручную. Если вы его найдете, вы увидите множество ссылок. Однако вы все равно должны понимать различие между логином и пользователем.
Полные права администратора для всего сервера или конкретной базы данных? Я думаю, что остальные ответили за базу данных, но за сервер:
USE ; GO CREATE LOGIN MyNewAdminUser WITH PASSWORD = N"abcd", CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; GO EXEC sp_addsrvrolemember @loginame = N"MyNewAdminUser", @rolename = N"sysadmin";
Вам может потребоваться оставить параметры CHECK_ в зависимости от того, какую версию SQL Server Express вы используете (почти всегда полезно включать эту информацию в ваш вопрос).
Если вы хотите создать общий script, вы можете сделать это с помощью инструкции Execute с заменой с вашим именем пользователя и именем базы данных
Declare @userName as varchar(50); Declare @defaultDataBaseName as varchar(50); Declare @LoginCreationScript as varchar(max); Declare @UserCreationScript as varchar(max); Declare @TempUserCreationScript as varchar(max); set @defaultDataBaseName = "data1"; set @userName = "domain\userName"; set @LoginCreationScript ="CREATE LOGIN [{userName}] FROM WINDOWS WITH DEFAULT_DATABASE ={dataBaseName}" set @UserCreationScript =" USE {dataBaseName} CREATE User [{userName}] for LOGIN [{userName}]; EXEC sp_addrolemember ""db_datareader"", ""{userName}""; EXEC sp_addrolemember ""db_datawriter"", ""{userName}""; Grant Execute on Schema:: dbo TO [{userName}];" /*Login creation*/ set @LoginCreationScript=Replace(Replace(@LoginCreationScript, "{userName}", @userName), "{dataBaseName}", @defaultDataBaseName) set @UserCreationScript =Replace(@UserCreationScript, "{userName}", @userName) Execute(@LoginCreationScript) /*User creation and role assignment*/ set @TempUserCreationScript =Replace(@UserCreationScript, "{dataBaseName}", @defaultDataBaseName) Execute(@TempUserCreationScript) set @TempUserCreationScript =Replace(@UserCreationScript, "{dataBaseName}", "db2") Execute(@TempUserCreationScript) set @TempUserCreationScript =Replace(@UserCreationScript, "{dataBaseName}", "db3") Execute(@TempUserCreationScript)
Вы можете использовать:
CREATE LOGIN
Вы также можете использовать:
GRANT permission [ ,...n ] ON SCHEMA:: schema_name
Чтобы настроить разрешения для схемы, которой вы назначили пользователям.
На прошлой неделе я установил версию Microsoft SQL Server 2014 Developer Edition на свою dev-страницу и сразу же столкнулся с проблемой, которую я никогда раньше не видел.
Я установил различные версии SQL Server бесчисленное количество раз, и это, как правило, безболезненная процедура. Установите сервер, запустите консоль управления, это так просто. Однако после завершения этой установки, когда я попытался войти на сервер с помощью SSMS, у меня появилась ошибка, подобная приведенной ниже:
Ошибка входа в SQL Server 18456 "Ошибка входа для пользователя... (Microsoft SQL Server, ошибка: 18456)" Я использовал эту ошибку, если я ввел неверный пароль при входе в систему - но это только если Im использует смешанный режим (Windows и SQL Authentication). В этом случае сервер был настроен только с помощью проверки подлинности Windows, а учетная запись пользователя была моей. Im все еще не уверен почему он не добавлял моего потребителя к роли SYSADMIN во время установки; возможно, я пропустил шаг и забыл добавить его. Во всяком случае, не всякая надежда была потеряна.
Способ исправить это, если вы не можете войти в систему с любой другой учетной записью на SQL Server, заключается в добавлении входа в сеть через интерфейс командной строки. Для этого вам необходимо быть администратором Windows для ПК, на который вы вошли.
Остановить службу MSSQL. Откройте командную строку, используя команду "Запуск от имени администратора". Перейдите в папку, в которой хранится EXE файл SQL Server; по умолчанию для SQL Server 2014 используется "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn". Выполните следующую команду: "sqlservr.exe -m". Это запустит SQL Server в однопользовательском режиме. Выйдя из этой командной строки, откройте еще один, повторите шаги 2 и 3. Во втором окне командной строки запустите "SQLCMD -S Server_Name\Instance_Name" В этом окне запустите следующие строки, нажав Enter после каждого из них: 1
СОЗДАТЬ ВХОД [имя_домена\имя_пользователя] ИЗ WINDOWS 2 ИДТИ 3 SP_ADDSRVROLEMEMBER "LOGIN_NAME", "SYSADMIN" 4 ИДТИ Используйте CTRL + C для завершения обоих процессов в окнах командной строки; вам будет предложено нажать Y, чтобы завершить процесс SQL Server.
Перезапустите службу MSSQL. Это оно! Теперь вы можете войти в систему, используя свой сетевой логин.
При развертывании информационных баз предприятий на основе решений 1С в клиент-серверном режиме с использованием СУБД MS SQL иногда бывает нужно, чтобы разные базы создавались от имени разных пользователей. Т.е. нам бывает нужно завести в SQL Management Studio пользователя, отличного от sa и ввести его данные в поля окна добавления новой ИБ. (рис1.)
Каковы минимальные права, при которых это всё будет функционировать?
В материалах методической поддержки ИТС говорится, что «этот пользователь должен иметь не только полные права на базу данных информационной базы, но и права на создание баз данных в SQL-сервере и на чтение таблиц базы данных Master». Чтобы посмотреть, как это работает на практике, проведем тестовую установку ИБ в клиент-серверном варианте, используя MS SQL Server 2008 R2 Express. Можно, конечно же, тупо скопировать параметры пользователя sa, но давайте сделаем это осмысленно, это всегда полезно.
Запустим среду SQL Management Studio 2008 R2, установим соединение с SQL-сервером и откроем раздел Безопасность->Имена входа и выберем команду контекстного меню «Создать имя входа», зададим имя пользователя и установим права dbcreator, public (рис.2)
На странице свойств пользователя «Сопоставление пользователей» отметим флажком «Схема» все базы в таблице сопоставленных пользователей master, model, msdb, tempdb, и для каждой базы из таблицы отметим членство в ролях public, db_owner (рис.3)
Теперь можно вернуться к окну, изображенному на рис. 1 и применить введенные параметры. Нажимаем Далее- > Готово и... база создана, список баз увеличился на одну позицию.
Таким образом, мы сможем обрадовать и успокоить системного администратора, ведь указанная комбинация прав пользователя MS SQL минимально достаточна для использования с платформой 1С в клиент-серверном режиме, и пароль «sa» останется не скомпрометированным, а у нас есть нужные нам права пользователя MS SQL.
