select sid, loginname, dbname from dbo.syslogins;
CREATE LOGIN [loginname] WITH PASSWORD=N'password', DEFAULT_DATABASE=[dbname ], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, SID = sid
select 'CREATE LOGIN [' + loginname + '] WITH PASSWORD=N''XXX'', DEFAULT_DATABASE=[' + dbname + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,SID =' + master.dbo.fn_varbintohexstr(sid) from dbo.syslogins Where loginname = 'username';
CREATE TABLE #Command ( Id int NOT NULL IDENTITY (1, 1), command nvarchar(MAX) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO USE database_name -- Use the database from which you want to extract the permissions GO SET NOCOUNT ON DECLARE @OldUser sysname, @NewUser sysname SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from SET @NewUser = 'userNEW' --The user or role to which to copy the permissions to insert into #Command(command) Select convert(nvarchar(max), '--Database Context') insert into #Command(command) SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) insert into #Command(command) SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) insert into #Command(command) SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships' FROM sys.database_role_members AS rm WHERE USER_NAME(rm.member_principal_id) = @OldUser ORDER BY rm.role_principal_id ASC insert into #Command(command) SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions' FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id WHERE usr.name = @OldUser ORDER BY perm.permission_name ASC, perm.state_desc ASC insert into #Command(command) SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions' FROM sys.database_permissions AS perm INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE usr.name = @OldUser AND perm.major_id = 0 ORDER BY perm.permission_name ASC, perm.state_desc ASC Select command from #Command order by Id drop table #Command
void getList() { var Q = TN.Admin.TCatOrderDeliveryRecord.Select().OrderBy("DeliveryCompletion_Date, Id Desc"); if (U2.WU.IsNonEmptyFromQueryStringOrForm("OD00")) { Q.And("OD00 = ", U2.WU.GetValue("OD00")); } if (U2.WU.V.StartDate_IsOK) { Q.And("DeliveryCompletion_Date >= ", U2.WU.V.StartDate); } if (U2.WU.V.EndDate_IsOK) { Q.And("DeliveryCompletion_Date < ", U2.WU.V.EndDate); } U2.JSON.WriteSuccessData(Q.GetPageDT2(U2.WU.V.CurrentPage, U2.WU.V.PageSize)); }