用SQL创建数据库登录用户

 

用SQL创建数据库登录用户

 

用户通过用来建立连接权限的登录（身份验证），获得 Microsoft® SQL Server™ 数据库的访问权限。因为 SQL Server 使用 Microsoft® Windows® 2005 身份验证，所以每个 SQL Server 登录必须与一个有效 Windows 帐户相关联。然后，可以为每个基于 Windows 用户或组帐户的 SQL 登录授予连接到 SQL Server 的权限。

注意   SQL Server 7.0 和 SQL Server 2000 不再独立于操作系统而管理组。SQL Server 较低版本中的组已被功能更强的角色所替代。但是，您可以在整个 Windows 组级管理 SQL Server 的安全。

如果您拥有 SQL Server 的系统管理员权限，则可以使用以下方法创建和修改 SQL Server 登录：

·         在SQL Server 2005新建查询中创建 SQL Server 登录

·         使用 SQL Server 工作流模板实例化向导创建 SQL Server 登录

当您使用向导从模板中创建工作流应用程序实例时，可以添加 SQL Server 登录。但是，只有当应用程序创建者拥有 SQL Server 系统管理员权限，并且 SQL Server 登录已经具有有效的 Windows 帐户时，此功能才有效。除非服务器管理员也为 modAppOwners 组授予了 SQL Server 系统管理员权限，否则 modAppOwners 成员不能创建 SQL Server 登录。

命名规则

用户名或组名不能与所管理的域或计算机的任何其它用户名或组名相同。它最多可包含 20 个大写或小写字符，但不能包含以下字符：

" / \ [ ] : ; | = , + * ? < >

用户名或组名不能只包含句点 (.) 和空格。

有关 SQL Server 登录的命名规则的详细信息，请参阅“SQL Server 联机丛书”。

  

SQL创建登录示例：

 

A.   创建具有主默认数据库的登录   ID  
  下例为用户   Victoria   创建一个   SQL   Server   登录，没有指定默认数据库。  
   
  EXEC   sp_addlogin   'Victoria',   'B1r12-36'  
   
  B.   创建登录   ID   和默认数据库  
  下例为用户   Albert   创建一个   SQL   Server   登录，并指定密码“B1r12-36”以及名为   corporate   的默认数据库。  
   
  EXEC   sp_addlogin   'Albert',   'B1r12-36',   'corporate'  
   
  C.创建使用其它默认语言的登录   ID  
  下例为用户   Claire   Picard   创建一个   SQL   Server   登录，密码为“B1r12-36”，默认数据库为   public_db，默认语言为   French。  
   
  EXEC   sp_addlogin   'Claire   Picard',   'B1r12-36',   'public_db',   'french'  
   
  D.   创建带有特定   SID   的登录   ID  
  下例为用户   Michael   创建一个   SQL   Server   登录，密码为“B1r12-36”，默认数据库为   pubs，默认语言为   us_english，SID   为   0x0123456789ABCDEF0123456789ABCDEF。  
   
  EXEC   sp_addlogin   'Michael',   'B1r12-36',   'pubs',   'us_english',   0x0123456789ABCDEF0123456789ABCDEF  
   
  E.   创建登录   ID   并且不加密密码  
  下例在   Server1   上为用户   Margaret   创建了一个密码为“B1r12-36”的   SQL   Server   登录，再析取此加密密码，然后使用前面加密的密码将用户登录   Margaret   添加到   Server2，但不对此密码进一步加密。之后，用户   Margaret   即可使用密码“Rose”登录到   Server2。  
   
  --   Server1  
  EXEC   sp_addlogin   'Margaret',   'B1r12-36'  
   
  --Results  
  New   login   created.  
   
  --   Extract   encrypted   password   for   Margaret  
  SELECT   CONVERT(VARBINARY(256),   password)  
        FROM   syslogins    
        WHERE   name   =   'Margaret'  

--Results  
  ------------------------------------------------------------------    
  0x0100163A5F026DA00F9FBCF3CB2E75B0C84887F3A87E191F8E0B7A2660F064A52B19590B9DE20D94DC0DFF857EDA  
   
  (1   row(s)   affected)  
   
  --   Server2  
  EXEC   sp_addlogin   'Margaret',   0x0100163A5F026DA00F9FBCF3CB2E75B0C84887F3A87E191F8E0B7A2660F064A52B19590B9DE20D94DC0DFF857EDA,    
        @encryptopt   =   'skip_encryption'  

 

 

 

 1 /**---建立营业主管登录帐户---*/
 2 EXEC sp_addlogin 'trafficManager', 'manager'
 3 /*---建立营业员登录帐户---*/
 4 EXEC sp_addlogin 'seller','seller'
 5 GO
 6 /*---建立数据库用户---*/
 7 USE productDB
 8 GO
 9 EXEC sp_grantdbaccess 'trafficManager','manager'
10 EXEC sp_grantdbaccess 'seller','seller'
11 GO
12 /*---向新建立的数据库用户授予权限---*/
13 USE productDB
14 GO
15 GRANT select,insert,update,delete on proInfo to manager
16 GRANT select on proInfo to seller
17 GO

 

 

 

USE master
GO
/*---检查是否已存在productDB数据库：查询master数据库中的系统表sysdatabases---*/
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'productDB')
    DROP DATABASE productDB
GO
EXEC xp_cmdshell 'mkdir D:\SQL'  --调用DOS命令创建文件夹，后续章节将讲解
/*-----建库--------*/
CREATE DATABASE productDB
 ON 
 (
  /*----数据文件的具体描述--*/
  NAME = 'proDB_data', --主数据文件的逻辑名
  FILENAME = 'D:\SQL\proDB_data.mdf' , --主数据文件的物理名
  SIZE = 10 MB,  --主数据文件初始大小
  FILEGROWTH = 20%   --主数据文件的增长率
 ) 
 LOG ON 
 (
  /*----日志文件的具体描述，各参数含义同上--*/
  NAME = 'proDB_log', 
  FILENAME = 'D:\SQL\proDB_log.ldf' ,
  SIZE = 1MB, 
  MAXSIZE = 10MB,
  FILEGROWTH = 20%
 )
GO
代码
---- 添加主键约束（stuNo作为主键）
ALTER TABLE stuInfo 
ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)
---添加唯一约束（身份证号唯一，因为每人的身份证号全国唯一）
ALTER TABLE stuInfo 
ADD CONSTRAINT UQ_stuID UNIQUE (stuID)
---添加默认约束（如果地址不填，默认为“地址不详”）
ALTER TABLE stuInfo 
ADD CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress
---添加检查check约束，要求年龄只能在15－40岁之间
ALTER TABLE stuInfo 
ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)
--添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）
ALTER TABLE stuMarks
   ADD CONSTRAINT FK_stuNo          
     FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)
GO

 

 

 

 

约束名的取名规则推荐采用：约束类型_约束字段

主键（Primary Key）约束：如 PK_stuNo

唯一（Unique Key）约束：如 UQ_stuID

默认（Default Key）约束：如 DF_stuAddress

检查（Check Key）约束：如 CK_stuAge

外键(Foreign Key)约束：如 FK_stuNo

 

 

 

Select bookid,bookname,价格=case 
when price='' then '价格未知' 
when price between 10 and 20 then '价格在10至20之间' 
when price between 20 and 30 then '价格适中' 
else cast(price as char(10))
end 
from books

 

 

 

USE stuDB
GO
--恢复原来的数据
--UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='李四' 
SET NOCOUNT ON --不显示受影响的行数信息
print '查看转帐事务前的余额'
SELECT * FROM bank  
GO

/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--*/
BEGIN TRANSACTION 
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT 
SET @errorSum=0  --初始化为0，即无错误

/*--转帐：张三的帐户少1000元，李四的帐户多1000元*/
UPDATE bank SET currentMoney=currentMoney-800 WHERE customerName='张三'
SET @errorSum=@errorSum+@@error  --累计是否有错误
UPDATE bank SET currentMoney=currentMoney+800 WHERE customerName='李四'
SET @errorSum=@errorSum+@@error  --累计是否有错误

print '查看转帐事务过程中的余额'
SELECT * FROM bank 

/*--根据是否有错误，确定事务是提交还是撤销---*/
IF @errorSum<>0  --如果有错误
  BEGIN
    print '交易失败，回滚事务'
    ROLLBACK TRANSACTION 
  END  
ELSE
  BEGIN
    print '交易成功，提交事务，写入硬盘，永久的保存'
    COMMIT TRANSACTION   
  END
GO

print '查看转帐事务后的余额'
SELECT * FROM bank  
GO
 
 
 
代码
USE stuDB
GO
/*--检测是否存在该索引(索引存放在系统表sysindexes中)----*/
IF EXISTS (SELECT name FROM sysindexes 
          WHERE name = 'IX_stuMarks_writtenExam')
   DROP INDEX stuMarks.IX_stuMarks_writtenExam  --删除索引
/*--笔试列创建聚集索引:填充因子为30％--*/
CREATE NONCLUSTERED INDEX IX_stuMarks_writtenExam
   ON stuMarks(writtenExam)
       WITH FILLFACTOR= 30
GO

/*--指定按索引：IX_stuMarks_writtenExam查询--*/
SELECT * FROM stuMarks 
  (INDEX=IX_stuMarks_writtenExam)
    WHERE writtenExam BETWEEN 60 AND 90
代码
 
 

-- Purpose: 常用系统存储过程使用

EXEC sp_databases  --列出当前系统中的数据库

EXEC  sp_renamedb 'Northwind','Northwind1'--改变数据库名称(单用户访问)

USE stuDB
GO

EXEC sp_tables  --当前数据库中查询的对象的列表

EXEC sp_columns stuInfo  --返回某个表列的信息

EXEC sp_help stuInfo  --查看表stuInfo的信息

EXEC sp_helpconstraint stuInfo --查看表stuInfo的约束

EXEC sp_helpindex stuMarks  --查看表stuMarks的索引

EXEC sp_helptext 'view_stuInfo_stuMarks' --查看视图的语句文本

EXEC sp_stored_procedures  --返回当前数据库中的存储过程列表