SQL Server/T-SQL Tutorial/User Role/LOGIN

Altering a SQL Server Login

ALTER LOGIN Veronica
WITH PASSWORD = "YourPassI"
OLD_PASSWORD = "YourPass"
ALTER LOGIN Veronica
WITH DEFAULT DATABASE = [YourDatabaseName]
ALTER LOGIN Veronica
WITH NAME = Angela,
PASSWORD = "BOS2004"

Altering a Windows Login

--ALTER LOGIN [YourID\Danny]
--DISABLE
--
--ALTER LOGIN [YourID\Danny]
--ENABLE
--
--ALTER LOGIN [YourID\DBAs]
--WITH DEFAULT_DATABASE = master

Create a proxy login from the certificate

4> CREATE LOGIN Dinesh
5> FROM CERTIFICATE Dinesh_Certificate
6> GO

Create a proxy user and grant access to the schema

5> CREATE USER Alejandro
6> WITHOUT LOGIN
7> GO
1>
2> GRANT SELECT ON SCHEMA::Sales
3> TO Alejandro
4> GO
Msg 15151, Level 16, State 1, Server J\SQLEXPRESS, Line 2
Cannot find the schema "Sales", because it does not exist or you do not have permission.

Creates a new Windows login and then maps that login to a new database user

USE Master
GO
CREATE LOGIN Adventureworks\Paul
 --USE AdventureWorks2000
GO
CREATE USER WindowsPaul FOR Login AdventureWorks\Paul
SQL Server 2000 SQL Server login:

Creating a SQL Server Login

CREATE LOGIN Veronica
WITH PASSWORD = "YourPass",
DEFAULT_DATABASE = YourDatabaseName
CREATE LOGIN Trishelle
WITH PASSWORD = "ChangeMe" MUST_CHANGE ,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON

Creating a Windows Login

CREATE LOGIN [YourID\Danny]
FROM WINDOWS
WITH DEFAULT_DATABASE = YourDatabaseName,
DEFAULT_LANGUAGE = English
GO
CREATE LOGIN [YourID\DBAs]
FROM WINDOWS
WITH DEFAULT_DATABASE= YourDatabaseName
GO

Denying SQL Server Access to a Windows User or Group

USE [master]
GO
DENY CONNECT SQL TO [YourID\TestUser]
GO
--To allow access again, you can use GRANT:
USE [master]
GO
GRANT CONNECT SQL TO [YourID\TestUser]
GO

DROP LOGIN

69> SELECT name, hasaccess, isntname, isntgroup, isntuser, sysadmin
70> FROM sys.syslogins
71> WHERE LEFT(name, 4) <> "##MS"
72> GO
name                                                                                                                             hasaccess   isntname    isntgroup   isntuser    sysadmin
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- -----------
sa                                                                                                                                         1           0           0           0           1
BUILTIN\Administrators                                                                                                                     1           1           1           0           1
NT AUTHORITY\SYSTEM                                                                                                                        1           1           0           1           1
J\SQLServer2005MSSQLUser$J$SQLEXPRESS                                                                                                      1           1           1           0           1
BUILTIN\Users                                                                                                                              1           1           1           0           0
(5 rows affected)
1>
2> IF EXISTS(SELECT * FROM sys.syslogins
3>     WHERE name = N"computername\winlogin1")
4>     DROP LOGIN [computername\winlogin1]
5> GO
1>
2> CREATE LOGIN [computername\winlogin1] FROM WINDOWS
3> GO
Msg 15401, Level 16, State 1, Server J\SQLEXPRESS, Line 2
Windows NT user or group "computername\winlogin1" not found. Check the name again.
1>
2> SELECT name, hasaccess, isntname, isntgroup, isntuser, sysadmin
3> FROM sys.syslogins
4> WHERE LEFT(name, 4) <> "##MS"
5> GO
name                                                                                                                             hasaccess   isntname    isntgroup   isntuser    sysadmin
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- -----------
sa                                                                                                                                         1           0           0           0           1
BUILTIN\Administrators                                                                                                                     1           1           1           0           1
NT AUTHORITY\SYSTEM                                                                                                                        1           1           0           1           1
J\SQLServer2005MSSQLUser$J$SQLEXPRESS                                                                                                      1           1           1           0           1
BUILTIN\Users                                                                                                                              1           1           1           0           0
(5 rows affected)
1>

Dropping a SQL Login

-- SQL Login
DROP LOGIN Angela

Dropping a Windows Login

-- Windows Group login
DROP LOGIN [YourID\DBAs]
-- Windows user login
DROP LOGIN [YourID\Danny]

Viewing SQL Server Logins

SELECT name, sid
FROM sys.server_principals
WHERE type_desc IN ("SQL_LOGIN")
ORDER BY name

Viewing Windows Logins

SELECT name, sid
FROM sys.server_principals
WHERE type_desc IN ("WINDOWS_LOGIN", "WINDOWS_GROUP")
ORDER BY type_desc
GO