Showing posts with label SQL SERVER SECURITY. Show all posts
Showing posts with label SQL SERVER SECURITY. Show all posts

Wednesday, 17 March 2021

CREATE NEW USER AND GRANT PERMISSION for INSER/UPDATE/DELETE IN SQL SERVER

 Use YourDatabase;

GO

--- Create User

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NewUserName')

BEGIN

    CREATE USER [NewAdminName] FOR LOGIN [NewAdminName]

    EXEC sp_addrolemember N'db_owner', N'NewUserName'

END;

GO



--database level permission

GRANT SELECT,UPDATE,INSERT,DELETE ON dbo.SampleDatabase TO user;

-- Schema level permission

GRANT SELECT,UPDATE,INSERT,DELETE ON SCHEMA::dbo TO user;

Tuesday, 19 May 2020

Check Performance in sql server

SELECT TOP 10 st.text
               ,st.dbid
               ,st.objectid
               ,qs.total_worker_time
               ,qs.last_worker_time
               ,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

Monday, 18 May 2020

Find Deadlock in sql server

The only other way I could suggest is digging through the information by using EXEC SP_LOCK (Soon to be deprecated), EXEC SP_WHO2 or the sys.dm_tran_locks table.

SELECT  L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName, 
    P.object_id AS LockedObjectId, 
    L.resource_type AS LockedResource, 
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,        
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

Sunday, 15 March 2020

how to create user in sql server and grant select permission only


Follow the 4 steps for creating user and grant permission:

Example:

step 1: create LOGIN rochelle with password ='admin@123'
step 2: use DatabaseName
step 3: create user s_rochelle for login rochelle
step 4: grant select to s_rochelle
Step 5: GRANT SELECT ON SCHEMA :: wmwhse2 TO DBUAT02 WITH GRANT OPTION;

Wednesday, 11 March 2020

Create new APPPOOL in sql server

IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOL\MLM')
BEGIN
    CREATE LOGIN [IIS APPPOOL\MLM]
      FROM WINDOWS WITH DEFAULT_DATABASE=[master],
      DEFAULT_LANGUAGE=[us_english]
END
GO
CREATE USER [MLMWebDatabaseUser]
  FOR LOGIN [IIS APPPOOL\MLM]
GO
EXEC sp_addrolemember 'db_owner', 'MLMWebDatabaseUser'
GO

redirect to new page from jquery

  function foo(id) { var url = ' @Url . Action ( "Details" , "Branch" , new { id = "__id__" })'...