SQLServer User and Login Tips

use master

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'gpdb83sp')
BEGIN
 DROP DATABASE gpdb83sp
END
;

CREATE DATABASE gpdb83sp
ON
( NAME = gpdb83sp_data,
   FILENAME = 'C:sql_data_filesgetpaidgpdb83sp.mdf',
   SIZE = 10MB,
   MAXSIZE = UNLIMITED,
   FILEGROWTH = 5MB )
LOG ON
( NAME = 'gpdb83sp_log',
   FILENAME = 'C:sql_data_filesgetpaidgpdb83sp.ldf',
   SIZE = 5MB,
   MAXSIZE = UNLIMITED,
   FILEGROWTH = 5MB )
;

IF NOT EXISTS (SELECT * FROM sys.syslogins where loginname = 'gpcomp1')
BEGIN
   CREATE LOGIN gpcomp1 WITH PASSWORD = 'gpcomp1', CHECK_POLICY = OFF   ;
END

-- GPCOMP1 ------------
EXEC sp_adduser gpcomp1;   --add user for the login gpcomp1, by default the new user will have the same name with login.
EXEC sp_addrolemember 'db_owner', 'gpcomp1'; --add the new user 'gpcomp1' to the db_owner role.
EXEC sp_password NULL, 'gpcomp1', 'gpcomp1';
EXEC sp_change_users_login 'Update_One', 'gpcomp1', 'gpcomp1';

 

sp_grantlogin -- sp_grantlogin 'PC-20131116WGRBAdministrator'
sp_addlogin    --user statement CREATE LOGIN  to instead if want to set the check_policy off;
sp_revokelogin
sp_denylogin
sp_helplogins --sp_helplogins 'gpcomp1'

sp_adduser
sp_dropuser
sp_helpuser --sp_helpuser 'gpcomp1' --sp_helpuser 'db_owner'

--server role are fixed in the database
--exec sp_helpsrvrole will display all the server role with discription
/*
sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators
*/
sp_addrole
sp_droprole
sp_helprole
sp_addrolemember
sp_addsrvrolemember
sp_helpsrvrole
sp_helpsrvrolemember
sp_dropsrvrolemember

sp_revokedbaccess -- have the same functionality with sp_adduser
sp_grantdbaccess  -- remove the dbuser  same as sp_dropuser

-- below stored procedure removed from SQLServer 2008
--sp_addgroup
--sp_changegroup
--sp_dropgroup
--sp_helpgroup

sp_helprole
sp_helprolemember
sp_helpsrvrole
sp_helpsrvrolemember
sp_helpuser gpcomp1
sp_helplogins 'gpcomp1'

 

-- from SQLSever 2008

http://msdn.microsoft.com/en-us/library/ms189751.aspx

create login

create user

create role

CREATE SYNONYM

CREATE SERVER ROLE

USE [TraceAnalysis]
GO
CREATE USER [TestUser] FOR LOGIN [gpcomp1] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TraceAnalysis]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [TestUser]
GO
USE [TraceAnalysis]
GO
EXEC sp_addrolemember N'db_owner', N'TestUser'
GO

 

CREATE SCHEMA [princesdSchema] AUTHORIZATION [princessd]
GO

use [TraceAnalysis]
GO
GRANT ALTER ON SCHEMA::[princesdSchema] TO [guest]
GO
use [TraceAnalysis]
GO
GRANT CONTROL ON SCHEMA::[princesdSchema] TO [guest]
GO
use [TraceAnalysis]
GO
GRANT DELETE ON SCHEMA::[princesdSchema] TO [guest]
GO
use [TraceAnalysis]
GO
GRANT EXECUTE ON SCHEMA::[princesdSchema] TO [guest]
GO

USE [TraceAnalysis]
GO
ALTER USER [princessd] WITH DEFAULT_SCHEMA=[princesdSchema]
GO

CREATE LOGIN LOGINTEST WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database user MB-Sales to login MaryB.
USE TraceAnalysis;
GO
EXEC sp_change_users_login 'Update_One', 'princessd', 'LOGINTEST';
GO

EXEC sp_revokedbaccess princessd   -- princessd name_in_db

exec sp_grantdbaccess princessd --princessd name of login

 

USE [master]
GO
CREATE LOGIN [aa] WITH PASSWORD=N'aa', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [aa]
GO
USE [AdventureWorks2012]
GO
CREATE USER [aa] FOR LOGIN [aa]
GO

--sysadmin 对应的database user/ default schma永远都是dbo!

sp_helprole
RoleName                                                                                                                         RoleId      IsAppRole
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
public                                                                                                                           0           0
db_owner                                                                                                                         16384       0
db_accessadmin                                                                                                                   16385       0
db_securityadmin                                                                                                                 16386       0
db_ddladmin                                                                                                                      16387       0
db_backupoperator                                                                                                                16389       0
db_datareader                                                                                                                    16390       0
db_datawriter                                                                                                                    16391       0
db_denydatareader                                                                                                                16392       0
db_denydatawriter                                                                                                                16393       0

sp_helprolemember

DbRole                                MemberName                      MemberSID
-----------------------------------------------------------------------------------
db_owner                              dbo            0x010500000000000515000000A065CF7E784B9B5FE77C877005E28000
db_owner                              person         0xC54BDED1EF72474AB8F063CFC38185AF

sp_helpsrvrole

ServerRole                          Description
----------------------------------- -----------------------------------
sysadmin                            System Administrators
securityadmin                       Security Administrators
serveradmin                         Server Administrators
setupadmin                          Setup Administrators
processadmin                        Process Administrators
diskadmin                           Disk Administrators
dbcreator                           Database Creators
bulkadmin                           Bulk Insert Administrators


sp_helpsrvrolemember 'sysadmin'

ServerRole               MemberName                              MemberSID
--------------------------------------------------------------------------------------------------------------------------
sysadmin                sa                                       0x01
sysadmin                PRINCESSDAdministrator                  0x0105000000000005150000001B41922AD288D1CE778B961BF4010000
sysadmin                NT SERVICESQLWriter                     0x010600000000000550000000732B9753646EF90356745CB675C3AA6CD6B4D28B
sysadmin                NT SERVICEWinmgmt                       0x0106000000000005500000005A048DDFF9C7430AB450D4E7477A2172AB4170F4
sysadmin               NT ServiceMSSQL$SQLEXPRESS2012           0x010600000000000550000000ACBBA1CE50EC42EB8976EA67374C8F3073529E2B


sp_helplogins 'person'

LoginName    SID                                       DefDBName                            DefLangName          AUser        ARemote
------------ -----------------------------------------------------------------------------------------------------------------------
person       0xC54BDED1EF72474AB8F063CFC38185AF        AdventureWorks2012                   us_english           yes            no     

LoginName    DBName                               UserName         UserOrAlias
------------ ------------------------------------ ---------------- -----------
person       AdventureWorks2012                   db_owner         MemberOf
person       AdventureWorks2012                   person           User


use AdventureWorks2012
GO
sp_helpuser 'person';

UserName     RoleName         LoginName    DefDBName                 DefSchemaName UserID     SID
------------ ---------------- ------------ ------------------------- ------------- ---------- --------
person       db_owner         person       AdventureWorks2012         person        5          0xC54BDED1EF72474AB8F063CFC38185AF

select SESSION_USER
GO
select CURRENT_USER
GO
select USER_NAME();
GO
 

select system_user

select name,loginname,* from sys.syslogins where sysadmin=1

select SESSION_USER
GO
select CURRENT_USER
GO
select USER_NAME()
GO

select user

select user_id()

select user_name(1)

select SUSER_SID()

select suser_sname()

select Host_Name()

-----------------------------查询server role 和与其关联的login---------------------

select 'ServerRole' = v1.name, 'Description' = v2.name
from master.dbo.spt_values v1, master.dbo.spt_values v2
where v1.low = 0 and
v1.type = 'SRV' and
v2.low = -1 and
v2.type = 'SRV' and
v1.number = v2.number

select serverroles.name,logins.name,logins.default_database_name 
from 
sys.server_principals logins,
sys.server_principals serverroles,
sys.server_role_members rolemember
where rolemember.role_principal_id =serverroles.principal_id 
and rolemember.member_principal_id=logins.principal_id


-----------------------------查询database role 和与其关联的用户---------------------
use AdventureWorks2012;
GO
DECLARE @role varchar(100)
SET @role = 'db_owner'
SELECT MemberName = Users.name, RoleName = Roles.Name
FROM sysusers Users, sysusers Roles, sysmembers Members
WHERE Roles.name = @role
AND Roles.uid = Members.groupuid
AND Roles.issqlrole = 1
AND Users.uid = Members.memberuid


select db_roles.name,database_users.name,database_users.default_schema_name 
from 
sys.database_principals database_users,
sys.database_principals db_roles,
sys.database_role_members db_role_memers
where db_role_memers.role_principal_id =db_roles.principal_id  and db_roles.type='R'
and db_role_memers.member_principal_id=database_users.principal_id  and database_users.type!='R'

用SQL语句实现 当前session里user的切换

-- 1. 显示当前测试环境
SELECT 
 Step = 'begin test', 
 original_login = ORIGINAL_LOGIN(), 
 current_login = SUSER_SNAME()

-- 2. 模拟 sa 登录
EXECUTE AS LOGIN = 'sa'
SELECT 
 Step = 'switch to sa', 
 original_login = ORIGINAL_LOGIN(), 
 current_login = SUSER_SNAME()

-- 3. 模拟 NT AUTHORITYSYSTEM 登录
EXECUTE AS LOGIN = 'NT AUTHORITYSYSTEM'
SELECT 
 Step = 'switch to NT AUTHORITYSYSTEM', 
 original_login = ORIGINAL_LOGIN(), 
 current_login = SUSER_SNAME()

-- 4. 恢复以前的执行上下文 1 
REVERT
SELECT 
 Step = 'first revert', 
 original_login = ORIGINAL_LOGIN(), 
 current_login = SUSER_SNAME()

-- 5. 恢复以前的执行上下文 2
REVERT
SELECT 
 Step = 'second revert', 
 original_login = ORIGINAL_LOGIN(), 
 current_login = SUSER_SNAME()

Step original_login current_login
---------- -------------------------------------- -----------------------------------------
begin test princessd princessd

Step original_login current_login
------------ -------------------------------------- ---------------------------------------
switch to sa princessd sa

Step original_login current_login
----------------------------- -------------------------------------- ----------------------
switch to NT AUTHORITYSYSTEM princessd NT AUTHORITYSYSTEM

Step original_login current_login
------------ -------------------------------------- ---------------------------------------
first revert princessd sa

Step original_login current_login
------------- -------------------------------------- --------------------------------------
second revert princessd princessd

---check the source code of a object------ 
select * from master.sys.syscomments 

 以下来自 http://wenku.baidu.com/link?url=SHLZKxb9MVTcJLLnrfIFwm6GietqWp99aMwCtgkCt5AiG-X4DKmI0C5IhwgC2TIWf5XQWFvsQUIvjqGPk8FPBbo01Yzr1s6pbSzwWOAMhpi

select @@SERVERNAME, @@SERVICENAME,  
ORIGINAL_LOGIN(), SYSTEM_USER, SUSER_NAME(),  HOST_NAME(), HOST_ID(),  
SESSION_USER, CURRENT_USER, USER, USER_NAME(),  USER_ID(), USER_SID()  


select cast(spid as 
varchar(5))+'/'+convert(char(19),login_time,121)+'/'+rtrim(hostname)+'/'+rtrim(program_name)+'/'+rtrim(nt_username)+'/'+net_address+'/'+rtrim(loginame) from master.dbo.sysprocesses where spid=@@SPID  
select spid, login_time, loginame, hostname, nt_domain, nt_username, net_address, net_library, program_name from master.sys.sysprocesses where spid = @@SPID  


select * from sys.dm_exec_connections where session_id = @@SPID  


select session_id, connect_time, auth_scheme, net_transport, client_net_address, client_tcp_port, connection_id from sys.dm_exec_connections where session_id = @@SPID  

有几个系统函数可返回用户名和用户 ID。若要了解这些函数的参数和输出，需要了解 SQL Server 中使用的名称和 ID 的类型。 
登录到 SQL Server 的每一个用户在 SQL Server 中都有以下两个级别的名称，每个名称都与一个唯一的 ID 相关联： 
登录名 
 
授权登录到 SQL Server 的每个用户都有一个登录名，该登录名将授予他们访问 SQL Server 实例的权限。有两种类型的登录名： 
Microsoft Windows 帐户名  
通过使用 sp_grantlogin，sysadmin 或 securityadmin 固定服务器角色的成员可以授权各个用户或 Windows 组的 Windows 帐户登录到 SQL Server 实例。然后，Windows 帐户标识的用户或 Windows 组中的任何用户可以使用 Windows 身份验证连接到 SQL Server 实例。每个 Windows 帐户或组名称都存储在 sys.server_principals 中。Windows 帐户或组的 Windows 安全标识符存储在 sys.server_principals.sid 中。 SQL Server 登录名 
 
这些名称将在用户使用 SQL Server 身份验证登录时使用。SQL Server 登录名由 sysadmin 或 securityadmin 固定服务器角色的成员使用 sp_addlogin 来定义的。每个 SQL Server 

登录名都存储在 master.dbo.syslogins.loginname 中。SQL Server 将生成一个用作安全标识符的 GUID 并将其存储在 sys.server_principals.sid 中。  
SQL Server 使用 sys.server_principals.sid 作为登录名的 security_identifier。 
数据库用户名 
 
每个 Windows 帐户或 SQL Server 登录都必须与已授权用户对其进行访问的每个数据库中的用户名相关联，或者该数据库必须已启用 guest 访问。数据库用户名由 db_owner 或 db_accessadmin 固定数据库角色的成员来定义，并存储在每个数据库的 sys.database_principals 表中。每个数据库用户名都与存储在 sys.database_principals.uid 中的一个数据库用户 ID 相关联。 
 
每个用户的security identifier都存储在 sys.database_principals.sid 中，因此可将用户映射回其关联登录名。如果使用同一数据库用户名作为 SQL Server 登录名或 Windows 帐户名，则可以减少混淆；但是，不要求一定这样做。 获得登录帐户或 ID 
连接到 SQL Server 实例时，可以使用下列函数获取登录帐户或 ID： 
 
SUSER_SNAME，以获取与security identifier关联的 SQL Server 登录名或 Windows 帐户。 
 
SUSER_SID，以获取与 SQL Server 登录名或 Windows 帐户关联的security identifier。 
 
SUSER_SID()（指定 SUSER_SID 时不带 login_account 参数），以获取当前连接的
security identifier，不管使用的是 SQL Server 身份验证还是 Windows 身份验证。 
 
ISO 函数 SYSTEM_USER，用来获取用于 Windows 身份验证连接的 Windows 帐户或用于 SQL Server 身份验证连接的 SQL Server 登录名。在 Transact-SQL 中，SYSTEM_USER 作为 SUSER_SNAME()（指定 SUSER_SNAME 时不带 security_identifier 参数）的同义词来实现。 
在 SQL Server 中，返回登录名或帐户的函数按照以下方式进行操作：  SUSER_SNAME(security_identifier) 
 
SUSER_SNAME 可以使用以下参数： 
 
用于 Windows 帐户或组的 security_identifier，在这种情况下，该函数将返回 Windows 帐户名或组名。 
 
为 SQL Server 登录名生成的伪 security_identifier，在这种情况下，该函数将返回 SQL Server 登录名。 

 

 

 
如果没有为使用 Windows 身份验证建立的连接指定 security_identifier，SUSER_SNAME 将返回与该连接关联的 Windows 帐户名。如果是使用 SQL Server 身份验证进行的连接，SUSER_SNAME 将返回与该连接关联的 SQL Server 登录。 
 SYSTEM_USER 
在 ISO 中，此函数作为 SUSER_SNAME() 的同义词实现。（指定 SUSER_SNAME 时不带 security_identifier 参数。） 获得数据库用户名或用户 ID 
连接到 SQL Server 实例时，可使用下列函数获取用户名或用户 ID： 
 USER_ID，以获取与数据库用户名关联的数据库用户 ID。  USER_ID()，以获取与当前连接关联的数据库用户 ID。  USER_NAME，以获取与数据库用户 ID 关联的数据库用户名。 
 
ISO CURRENT_USER 或 SESSION_USER 函数，用来获取与当前连接关联的数据库用户名。在 Transact-SQL 中，这些函数实施为 USER_NAME() 的同义词。（指定 USER_NAME 时不带 database_user_ID 参数。）Transact-SQL 函数 USER 也实现为 USER_NAME() 的同义词。  
ISO 允许在符合如下条件的 SQL 模块中对 SQL 语句进行编码：模块的授权标识符独立于已连接到 SQL 数据库的用户的授权标识符。ISO 指定 SESSION_USER 始终返回建立连接的用户的授权标识符。对于从 SQL 模块中执行的任何语句，CURRENT_USER 都将返回 SQL 模块的授权标识符；如果 SQL 语句不是从 SQL 模块中执行的，则返回进行连接的用户的授权标识符。如果 SQL 模块没有独立的授权标识符，ISO 将指定 CURRENT_USER 返回与 SESSION_USER 相同的值。SQL Server 没有用于 SQL 模块的独立授权标识符；因此 CURRENT_USER 和 SESSION_USER 始终相同。ISO 将 USER 函数定义为向后兼容按照早期版本的标准编写的应用程序的函数。USER 被指定为与 CURRENT_USER 返回相同的值。 
在 SQL Server 中，返回登录名或帐户的函数按照以下方式进行操作： 
 
USER_ID('database_user_name')  
 
USER_ID 将返回与指定数据库用户名关联的数据库用户 ID。如果未指定 
database_user_name，USER_ID 将返回与当前连接关联的数据库用户 ID。 
 
USER_NAME(database_user_ID)  
USER_NAME 返回与指定的数据库用户 ID 关联的数据库用户名。如果未指定 
database_user_ID，USER_NAME 将返回与当前连接关联的数据库用户名。 
 
CURRENT_USER、SESSION_USER、USER  

这些函数是 USER_NAME() 的同义词。（指定 USER NAME 时不带 database_user_ID 参数。）