platformaerp.com

CREATE USER IN DB

-- prefix user_ dla aplikacji np user_ email reszty uzytkownicy
DECLARE @database NVARCHAR(MAX) = 'db_app' ;    -- Replace with your database name
DECLARE @username NVARCHAR(MAX) = 'user_app';   -- Replace with desired username
                        -- for system users use 'user_dbname'
                        -- for support users use 'xxxx@platformaerp.com'
DECLARE @password NVARCHAR(MAX) = newID();      -- Replace with desired password

-- Create login on the server
DECLARE @createLogin NVARCHAR(MAX) = 
    'CREATE LOGIN [' + @username + '] WITH PASSWORD = ''' + @password + ''';';
EXEC sp_executesql @createLogin;

-- Set default database
DECLARE @setDefaultDB NVARCHAR(MAX) = 
    'ALTER LOGIN [' + @username + '] WITH DEFAULT_DATABASE = [' + @database + '];';
EXEC sp_executesql @setDefaultDB;

-- Use the specified database and create a user
DECLARE @createUser NVARCHAR(MAX) = 
    'USE [' + @database + ']; 
    CREATE USER [' + @username + '] FOR LOGIN [' + @username + '];  
    GRANT EXECUTE TO [' + @username + '];
    EXEC sp_addrolemember ''db_owner'', ''' + @username + ''';';
EXEC sp_executesql @createUser;
print 'username ' + @username
print 'password ' + @password
print 'Data Source=xxx.xxx.xxx.xxx;Initial Catalog='+@database+';User ID='+@username+';Password='+@password+';'

-- NOTES:
-- GRANT VIEW SERVER STATE TO [xxx@platformaerp.com] -- only for support users
-- GRANT ALTER TRACE TO [UserName];   -- for profiler

Change password

ALTER LOGIN [xxxxxxxxx@platformaerp.com] WITH 
     PASSWORD = 'xxxxxxxxxxxx' 
     OLD_PASSWORD = 'xxxxxxxxxxxxxxx';

Delete user

EXECUTE AS LOGIN = 'xxx@platformaerp.com'
    SELECT 'use ' + name +'; DROP USER [xxx@platformaerp.com];'
    FROM MASTER.sys.databases
    WHERE HAS_DBACCESS([name]) = 1
REVERT

Drop login [xxx@platformaerp.com]

Hide databases

Warning

Do not work well

USE master;

REVOKE VIEW ANY DATABASE TO PUBLIC;

List members of any roles in current databse

SELECT
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName,
    database_role_members.member_principal_id AS MemberPrincipalID,
    members.name AS MemberPrincipalName
FROM
    sys.database_role_members AS database_role_members
JOIN
    sys.database_principals AS roles
ON
    database_role_members.role_principal_id = roles.principal_id
JOIN
    sys.database_principals AS members
ON
    database_role_members.member_principal_id = members.principal_id;

SELECT pr.name, pe.permission_name, pe.state_desc
FROM sys.database_principals pr
JOIN sys.database_permissions pe ON pr.principal_id = pe.grantee_principal_id
WHERE permission_name = 'ALTER TRACE';