Bookmark and Share
User accounts admin useful commands
(Publish Date: 2014-5-21 11:33am, Total Visits: 269, Today: 1, This Week: 1, This Month: 2)

Query the list of existing logins

SELECT * FROM sys.server_principals where type IN ('S','U','G');

GO

CREATE LOGIN HRApp WITH PASSWORD = 'Pa$$w0rd', CHECK POLICY = OFF;

GO

SELECT * FROM sys.sql_logins;

GO

USE Market;

SELECT * FROM sys.user_token;

GO

-- Query sid of an username at the server level and at the database level

SELECT name, prinvipal_id, sid FROM sys.server_principals WHERE name = ‘TestUser’;

GO

SELECT name, prinvipal_id, sid FROM sys.database_principals WHERE name = ‘TestUser’;

GO

-- Alter user to make the sid of an username at the server level same as the one at the database level.

ALTER USER TestUser WITH LOGIN = TestUser;

GO

n  -- Create logins for groups that are needed

CREATE LOGIN [SoftwareDev\ITsupport] FROM WINDOWS;

GO

n  -- Create individual Windows logins that are needed

CREATE LOGIN [SoftwareDev\Dan.Young] FROM WINDOWS;

GO

n  -- Create individual SQL logins that are needed

CREATE LOGIN DBadmin WITH PASSWORD = ‘XXXXX’,  CHECK_POLICY = OFF;

GO

n  Alter login

ALTER LOGIN DBadmin WITH PASSWORD = ‘XXXXX’;

GO

ALTER LOGIN DBadmin WITH CHECK_POLICY = OFF;

GO

n  Create users for groups that are needed

CREATE USER [SoftwareDev\ITsupport] FOR LOGIN [SoftwareDev\ITsupport];

GO

n  Create users individual windows logins that are needed

CREATE USER [SoftwareDev\Dan.Young] FOR LOGIN [SoftwareDev\Dan.Young];

GO

n  Create users for individual SQL logins that are needed

CREATE USER DBadmin FOR LOGIN DBadmin;

GO