create "userid" for login "database" 를 하고 grant 작업이 진행이 되면서 각 DB에 사용자를 다시 만들기 때문이다
이때 발생 에러메세지로
제목: Microsoft SQL Server Management Studio "USERID"사용자 'DATABASE'에 대한 만들기이(가) 실패했습니다. (Microsoft.SqlServer.Smo) 추가 정보: Transact-SQL 문 또는 일괄 처리를 실행하는 동안 예외가 발생했습니다.(Microsoft.SqlServer.ConnectionInfo) 현재 데이터베이스에 사용자, 그룹 또는 역할 'GRANT'이(가) 이미 있습니다. (Microsoft SQL Server, 오류: 15023)
mssql 에서 활성 중인 또는 실행 대기 중인 쿼리와 세션을 확인 하여 lock에 걸린 문제의 세션을 kill 하기.
--세션 SELECT sqltext.TEXT, req.session_id, req.status, req.start_time, req.command, req.cpu_time, req.wait_time, req.total_elapsed_time, text_size FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext order by sqltext.TEXT, req.status ;
-- lock SELECT DISTINCT name AS database_name, session_id, host_name, login_time, login_name, reads, writes FROM sys.dm_exec_sessions LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id WHERE resource_type <> 'DATABASE' AND request_mode LIKE '%X%' --AND name ='YG1MES' ORDER BY name ;
SQL Server Error Messages - Msg 8101 Error Message Causes This error happens when you are trying to insert a new record into a table that contains an identity column without specifying the columns in the INSERT statement and you are assigning a value to the identity column instead of letting SQL Server assign the value.
To illustrate on how the error can be encountered:
CREATE TABLE [dbo].[Users] (
[UserID] INT NOT NULL IDENTITY(1, 1),
[UserName] VARCHAR(20) NOT NULL,
[FirstName] VARCHAR(50) NOT NULL,
[LastName] VARCHAR(50) NOT NULL
)
GO
INSERT INTO [dbo].[Users]
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'dbo.Users' can only be specified when a
column list is used and IDENTITY_INSERT is ON.
If you specified the column names in the INSERT statement, you will get a different error message:
INSERT INTO [dbo].[Users] ( [UserID], [UserName], [FirstName], [LastName] )
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Users' when
IDENTITY_INSERT is set to OFF.
Solution / Workaround:
There are two ways of avoiding any of the errors mentioned above. The first option is not to include the identity column in the INSERT statement and let SQL Server assign the next identity value to the record:
INSERT INTO [dbo].[Users] ( [UserName], [FirstName], [LastName] )
VALUES ( 'superman', 'Clark', 'Kent' )
GO
The second option is to enable the IDENTITY_INSERT property for the table. If you really want to specify the value for the identity column, this option is the one for you.
SET IDENTITY_INSERT [dbo].[Users] ON
GO
INSERT INTO [dbo].[Users] ( [UserID], [UserName], [FirstName], [LastName] )
VALUES ( 1, 'superman', 'Clark', 'Kent' )
GO
SET IDENTITY_INSERT [dbo].[Users] OFF
GO
Setting the IDENTITY_INSERT to ON for the table allows explicit values to be inserted into the identity column of a table. At any given time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON and a SET IDENTITY_INSERT ON statement is issued on another table, SQL Server will return the following error message:
Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'dbo.Deparments'.
Cannot perform SET operation for table 'dbo.Users'.
Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.
