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_files\getpaid\gpdb83sp.mdf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB )
LOG ON
( NAME = 'gpdb83sp_log',
FILENAME = 'C:\sql_data_files\getpaid\gpdb83sp.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-20131116WGRB\Administrator'
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 PRINCESSD\Administrator 0x0105000000000005150000001B41922AD288D1CE778B961BF4010000 sysadmin NT SERVICE\SQLWriter 0x010600000000000550000000732B9753646EF90356745CB675C3AA6CD6B4D28B sysadmin NT SERVICE\Winmgmt 0x0106000000000005500000005A048DDFF9C7430AB450D4E7477A2172AB4170F4 sysadmin NT Service\MSSQL$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 AUTHORITY\SYSTEM 登录 EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM' SELECT Step = 'switch to NT AUTHORITY\SYSTEM', 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 AUTHORITY\SYSTEM princessd NT AUTHORITY\SYSTEM
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 参数。）
